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
END