Solved

New to Triggers, Update Pricing

Posted on 2014-02-24
4
180 Views
Last Modified: 2014-02-25
Hello all,

I am new to triggers and need help with this first one:

I have two tables:

PricingLevels which is a list of pricing levels

PricingLevelID (PK)     PricingLevelName            InheritsFrom            InheritFactor
--------------------------------------------------------------------------------------------------------
  1                               Default                                   NULL                          NULL
  2                               Special                                     1                                .98

In the above small example "Special" Pricing is 98% of "Default" (or a 2% discount)

I have another table called "Prices" which holds an entry for all items for each level.

PricingLevelID (FK to PricingLevels)              ItemNo               Price
-----------------------------------------------------------------------------------------
1                                                                     1000                   $100
2                                                                     1000                   $98

I want to create a trigger that when an item in "Default" pricing is changed, it would fire and update the same item in "Special" since Special inherits from Default.

In other words, if the price of Item 1000 is changed from $100 to $200 on pricing level 1 (Default), pricing level 2 (Special) would change to $196 via a trigger.  

The trigger should look for all instances of the items which are in pricing levels that inherit from the updated item.

Thanks in advance.
0
Comment
Question by:ccleebelt
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39884449
ALTER TRIGGER Prices__TRG_UPD_Autoadjust_Inherited_Prices
ON dbo.Prices
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(Price)
BEGIN
    UPDATE p
    SET Price = (i.price * 1.0) / d.price * p.price
    FROM inserted i
    INNER JOIN deleted d ON
        d.PricingLevelID = i.PricingLevelID AND
        d.ItemNo = i.ItemNo AND
        d.Price <> i.Price
    INNER JOIN dbo.PricingLevels pl_default ON
        pl_default.PricingLevelID = i.PricingLevelID AND
        pl_default.InheritsFrom IS NULL
    INNER JOIN dbo.PricingLevels pl_inherits ON
        pl_inherits.InheritsFrom = pl_default.PricingLevelID
    INNER JOIN dbo.Prices p ON
        p.PricingLevelID = pl_inherits.PricingLevelID
END --IF
0
 

Author Comment

by:ccleebelt
ID: 39884486
Scott - thanks for this...super close.

The only nit is that I need to set the price of other items to the price of the updated item *  PricingLevel.InheritFactor

.. not sure how to join all that in there.

Thanks again.
0
 

Author Comment

by:ccleebelt
ID: 39884489
Nevermind .. got it!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39885718
>> The only nit is that I need to set the price of other items to the price of the updated item *  PricingLevel.InheritFactor <<

D'OH, quite right, sorry, I overlooked that.

But you got it, so we're good.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now