SQL server 2005 provides the records being inserted / modified or deleted in two temporary tables available in a DML trigger. These tables are INSERTED and DELETED. The INSERTED table has the new state records being inserted or updated. The DELETED table has the old state of the records being updated or deleted. Obviously for an insert operaion DELETED table would have zero records. Similarly, for a delete operation INSERTED table would be empty. And for a Update operation both tables should have some records.
The above theory about number of records is true when the insert / update/ delete operation result in some rows being inserted/ updated/ deleted. So if no record is selected because of the criteria of delete or update statement. Then even in case of delete or update both tables i.e. INSERTED and DELETED would be empty. But if you would have to log the activity then you would not want to log the attempt which has modified no record.
I used this approach to find out the operation which has caused the trigger to fire. The trigger was set to fire for insert, update or delete operation. I had to log each modification of data. The above approach of record cound in INSERTED and DELETED table worked perfectly fine for me.
I will be coming with more blogs about SQL server trigger in coming days.
1-
Subscribe to:
Post Comments (Atom)
6 comments:
Oooopsss,
why I can't see INSERTED/DELETED
tables ???
If i write select * from INSERTED,
I get an error :
in sum it says : word inserted is not valid...
?????????????? why ??
These temporary tables are only available in trigger body or in OUTPUT clause of a DML statement.
Sir,
Plz gv me the OUTPUT CLAUSE SYNTAX OR EXAMPLE FOR USING INSERTED/DELETED TABLES IN SQLSERVER WITHOUT USING TRIGGERS.
clear concept,thanks
I was looking for capturing table changes using trigger and guess whose blog I come across "Shujaat"
Thanks shashi for liking this. I hope you are fine.
Post a Comment