Does anyone know how to right a trigger that will update Price level discounts? I.E. set Price1A to 1% less than the actual item.price.
This is what I have but it does not work as of yet, I am missing something in the code:
CREATE TRIGGER pricechange ON Item FOR UPDATE AS if update (item) begin set dbo.QuantityDiscount.Price1A =((item.Price)-(item.Price * 0.01)) from quantitydiscount, item where QuantityDiscount.Description = Item.ItemLookupCode end
UPDATE Item SET PriceA = inserted.Price - (inserted.Price * 0.01) FROM inserted WHERE Item.ID = inserted.ID
:)
Please use primary key for compare two records..
bye antonio
Rob F.
There are two triggers one that update on the Price field updating and the other on an item being created(Insert).
CREATE TRIGGER [utr_Item_Price_PriceA_Insert] ON [Item] FOR INSERT AS
/*********************************************************** utr_Item_Price_PriceA_Insert sets Price A to PriceA to 1% less than the actual item.price.
************************************************************/ BEGIN UPDATE PriceA = =(Item.Price) -(Item.Price * 0.01) FROM Item INNER JOIN INSERTED i on Item.ItemLookupCode = i.ItemLookupcodeEND
---------------------------------------------------------------------------------------
CREATE TRIGGER [utr_Item_Price_PriceA_Update] ON [Item] FOR UPDATE AS
/*********************************************************** utr_Item_Cost_PriceA_Insert sets Price A to PriceA to 1% less than the actual item.price.
************************************************************/ BEGIN IF UPDATE(Price) UPDATE PriceA = =(Item.Price) -(Item.Price * 0.01) FROM Item INNER JOIN INSERTED i on Item.ItemLookupCode = i.ItemLookupcodeEND
This is to delete a trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name 'insert_name_of_trigger_here_with_quotes' AND type = 'TR') DROP TRIGGER insert_name_of_trigger_here_no_quotes
Rob
We came up with this that works, generic percentages of course:
CREATE trigger pricechange on item for update as if update (price) begin
update quantitydiscount set quantitydiscount.price1=(item.price-(item.price*0.01)), quantitydiscount.price2=(item.price-(item.price*0.02)), quantitydiscount.price3=(item.price-(item.price*0.03)), quantitydiscount.price4=(item.price-(item.price*0.04)), quantitydiscount.price1a=(item.price-(item.price*0.01)), quantitydiscount.price2a=(item.price-(item.price*0.02)), quantitydiscount.price3a=(item.price-(item.price*0.03)), quantitydiscount.price4a=(item.price-(item.price*0.04)), quantitydiscount.price1b=(item.price-(item.price*0.02)), quantitydiscount.price2b=(item.price-(item.price*0.03)), quantitydiscount.price3b=(item.price-(item.price*0.04)), quantitydiscount.price4b=(item.price-(item.price*0.05)), quantitydiscount.price1c=(item.price-(item.price*0.03)), quantitydiscount.price2c=(item.price-(item.price*0.04)), quantitydiscount.price3c=(item.price-(item.price*0.05)), quantitydiscount.price4c=(item.price-(item.price*0.06))
from quantitydiscount, item where quantitydiscount.id=item.quantitydiscountid
end
We are going to use SubDescription1 to determine which categories or items get set to which percentages, we just need to figure out the if then statement in the trigger.
Thanks,
"Ant> UPDATE Item SET PriceA = inserted.Price - (inserted.Price * 0.01)
Rob F.
Sorry there should only be a single equals sign. UPDATE PriceA Item.Price) -(Item.Price * 0.01)
Rob
---------------------------------------------------------------------------------------
I have a question Rob.. Why you post your trigger if the business rules are of your customer?
It's a good idea to post SQL CODE that solve bugs of RMS or to provide a new function for this system...
Have something to add? Share your thoughts — no account required.
Ask the community — no account required