3

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

1 Answer 1

10

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
3
  • This is exactly what I was looking for ! Trigger on Update with a formula for valueDelta = new-old . I was looking for the Before Update and did not know deleted meant original values .. perhaps MS should have named it original rows ..or previous.
    – StixO
    May 7, 2020 at 8:38
  • 1
    @StixO, blame Sybase rather than Microsoft for the virtual table names. These names are artifacts from the port of Sybase SQL Server to OS/2 and Windows Intel platforms circa 1990.
    – Dan Guzman
    May 7, 2020 at 9:42
  • Dan Guzman - I meant to add - amazing someone else that knows what OS/2 is! :-) Some functionality in that OS (circa 2000) still has windows beat , Multi-Tasking , and threading in particular!
    – StixO
    Jun 22, 2020 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.