Wednesday, April 30, 2008

Operation causing trigger to fire

Oracle has two options for DML triggers on a table. 1- They can be set up to fire for each row modified. 2- The can be set for each statement executed e.g. Insert, update, delete. I learnt an awkward thing about SQL server 2005 DML trigger today that it can not be set for execution for each row being inserted, deleted or modified.

But there are workarounds through which you can achieve the same result. For the work around you must remember the INSERTED and DELETED tables available in a DML trigger.

What you can do is to find out the count on these two tables .

Select @InsertedCount = count(*) from INSERTED;
Select @DeletedCount = count(*) from DELETED;

where the variables are declared as:

Declare @InsertedCount, @DeletedCount int;

Now if @InsertedCount > 0 and @DeletedCount = 0, it means that it is an insert operation.

if @InsertedCount = 0 and @DeletedCount > 0, it means that it is a delete operation.

If @InsertedCount > 0 and @DeletedCount > 0, it means that it is an update operation.

The last condition is if @InsertedCount = 0 and @DeletedCount = 0. This is not a hypothetical situation. But a real situtation. This situation can occur as a result of Update or Delete query which has selected no record to modify / delete because of the selection criteria in WHERE clause.

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.