INSERTED / DELETED tables in a SQL Server trigger

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-