Solved

New to Triggers, Update Pricing

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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 Copy Database Wizard 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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 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

10 Experts available now in Live!

Get 1:1 Help Now