Solved

New to Triggers, Update Pricing

Posted on 2014-02-24
4
184 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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 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.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

15 Experts available now in Live!

Get 1:1 Help Now