Before and After update trigger on same table

Question

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

Answer 1

There is no BEFORE trigger in SQL Server. An INSTEAD OF trigger can be used to provide 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;

GO