Solved

New to Triggers, Update Pricing

Posted on 2014-02-24
4
190 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:
Scott Pletcher 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:Scott Pletcher
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 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