Update Price1A Trigger

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.ItemLookupcode

END

---------------------------------------------------------------------------------------

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.ItemLookupcode

END

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...