SQL Server Performance

Compare old and new values in trigger

Discussion in 'General Developer Questions' started by gv_pradeep, Aug 12, 2008.

  1. gv_pradeep New Member

    Hi,
    I've attached a trigger on update. Shown below.
    CREATE TRIGGER Trig_TXNMaster_Amount_Update
    ON TXNMaster
    FOR UPDATE
    AS BEGIN
    UPDATE t
    SET AMOUNT = b.AMOUNT
    FROM TXNMaster t
    INNER JOIN inserted a ON t.ID = a.ID -- Picks a unique row
    INNER JOIN deleted b on t.ID=b.ID -- Picks a unique row
    WHERE a.AMOUNT>b.AMOUNT -- If New value is greater, replace it with old value itself
    END
    It checks each row and resets to old value if the new value is faulty. This works fine. But I guess there is a performance hit as it checks each row again and replaces if necessary.
    I guess it would be simpler if i could achieve this by rolling back the wrong transactions. Can you help me on how i can achieve this.?
    Thanks,
    Jeeves
  2. Adriaan New Member

    Even simpler would be to prevent this in the procedure that executes the update.
    Other than that, the use of the snapshots with inner joins means only the rows affected will be evaluated, not the entire table.

Share This Page