Triggers–do they need data changes to fire?

It may surprise some to know that a trigger does not require a change to the table to fire. This post will explore what happens when a trigger fires but no data is changed.

First, I will create a table and a trigger.

create table Table1(id int , Iname varchar(250))
go

create trigger trg_insTable1 on Table1
for insert
as
raiserror ('The insert trigger is fired',10,16) 

declare @i int
select @i = count(*) from inserted
if @i > 0
begin
declare @msg as varchar(29) = 'there are ' + cast(@i as varchar(3)) + ' row(s) on inserted'
raiserror (@msg,10,16)
end 

I will now test the trigger by the following TSQL:

insert into Table1(id ,Iname )
select 1,'test' where 1=0

This TSQL returns:

The insert trigger is fired
(0 row(s) affected)

In this test, the ‘WHERE 1=0’ ensures that no data will be generated. However, the trigger will fire – as is indicated by the results. This may appear surprising; a trigger has fires without any data being inserted. This is the behaviour expected. From Books Online –  “triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.”

http://msdn.microsoft.com/en-us/library/ms189799.aspx

In general, this behaviour is probably not an issue. If the trigger uses the inserted table, then the table will be empty and any operations based on this table will not run. This is shown in my example – there are no rows in the inserted table. However, if the trigger does not use the inserted table, this will be an issue.

To demonstrate the way the trigger works where the table changes, I will run the following statement –

insert into Table1(id ,Iname )
select 1,type_desc from sys.objects 

This TSQL returns – as expected – confirmation that the table has fired, then a count of the number of rows on the inserted table (there are 93 rows on my sys.objcts table).

The insert trigger is fired
there are 93 row(s) on insert

To conclude, triggers will fire whenever a DML statement is executed. The developer must ensure that the code in the trigger will function correctly in these conditions.