How to solve the Before and After update trigger on the same table?
I have a table with 4 columns qty, qtydiff, price and value. I want a after update trigger that fires when qty row values change (more than one rows). The Before trigger should read and store the old values from qty and After trigger should subtract old value from new value on qty column, get the difference value in qtydiff and multiply that with price column and update the result in the value column. The code below is just a concept code that I have not tested yet, I am not sure it would work or not but is there a way to get before and after update to work in the same trigger? CREATE TRIGGER [dbo].[Price_Modified] ON [dbo].[STOCK] BEFORE UPDATE ON STOCK AS BEGIN SET NOCOUNT ON; IF UPDATE (Qty) BEGIN SELECT qty FROM STOCK END AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF UPDATE (Qty) BEGIN UPDATE Stock SET value = price * qtydiff FROM STOCK WHERE qtydiff = oldvalue - newvalue END
FYI there is no BEFORE trigger in SQL Server. An INSTEAD OF trigger can be utilized to serve similar functionality but the trigger code would need to perform the UPDATE. However, an AFTER trigger can be used here by using the INSERTED (new) and DELETED (old) virtual tables to get the values needed for the calculation. The example below assumes a primary key column named StockID with a value that cannot be changed. CREATE TRIGGER [dbo].[Price_Modified] ON [dbo].[STOCK] AFTER UPDATE AS SET NOCOUNT ON; IF UPDATE (Qty) BEGIN UPDATE s SET value = new.price * (new.Qty - old.Qty) FROM STOCK AS s JOIN inserted AS new ON new.StockID = s.StockID JOIN deleted AS old ON old.StockID = s.StockID WHERE new.Qty <> old.Qty; END; Hope this helps you!