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 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 their 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 END