• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

New to Triggers, Update Pricing

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
ccleebelt
Asked:
ccleebelt
  • 2
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
ccleebeltAuthor Commented:
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
 
ccleebeltAuthor Commented:
Nevermind .. got it!
0
 
Scott PletcherSenior DBACommented:
>> 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now