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

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.