How can catch the modified row(s) in update trigger to insert them into other table ?
In SQL Server we have only two virtual tables you can workwith inside a trigger, those are “INSERTED” and “DELETED”. For example:
Insert Operation:When you insert a new record “INSERTED” virtual table contains the newly inserted record, where as “DELETED” virtual remains empty.
Update Operation:When you update any record, first the old record will be placed into the “DELETED” virtual table and the newly updated record is hold by the “INSERTED” virtual table.
That means you can get the old value from “DELETED” and the currently updating value through “INSERTED” virtual table. you can query them like:
-- To get the old record value SELECT * FROM DELETED -- To get the updated value SELECT * FROM INSERTED
Delete Operation:When you delete any particular record the deleted record will be inserted into the “DELETED” virtual table.
SELECT * FROM UPDATED
– gives ERROR.
DECLARE @OldVal int, @NewVal int
SELECT @OldVal = Col FROM DELETED
SELECT @NewVal = Col FROM INSERTED
by holding the old and new values you can compare their state.