SQL Server: trigger for insert update and delete caught when no data been changed?

Question

I've added a simple table to a db called: aaa_log with columns :(id ,name, op))

CREATE TABLE aaa_log (
 [id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
 [name] [varchar](50) NOT NULL,
 [op] [varchar](50) NULL)

id column used just in manner to keep the order.

I've added a trigger (for insert, update, delete) to all other tables in a db ussing the following script:

declare @cmd varchar(max)
declare trigger_create cursor for
select 'Create trigger ['+TABLE_SCHEMA+'].[xxtr_'+TABLE_NAME+'_auto]
 on ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] fro insert,update,delete as
 BEGIN
 declare @op varchar(20)
 if exists(SELECT * from inserted) and exists(SELECT * from deleted)
 begin
 set @op = ''update''
 end
 if exists(SELECT * from inserted) and not exists(SELECT * from deleted)
 begin
 set @op = ''insert''
 end
 if not exists(SELECT * from inserted) and exists(SELECT * from deleted)
 begin
 set @op = ''delete''
 end
 insert into aaa_log([name],[op]) values('+TABLE_SCHEMA+'.'+TABLE_NAME+', @op)
 END
'
from information_schema.tables
where table_type='BASE TABLE' AND table_name <> 'aaa_log'
open trigger_create
fetch next from trigger_create into @sql
while @@FETCH_STATUS =0
BEGIN 
 exec(@sql)
 fetch next from trigger_create into @sql
END
close trigger_create
deallocate trigger_create

Nobody else update the aaa_log table, just these triggers, but when I check the aaa_log table I see some rows where op is NULL.

The only option I can think off is that both inserted and updated is null, so how the trigger had been activated?

Any explenation?

• Dan Guzman: What do you expect if no rows are affected, such as DELETE TOP(0) FROM aaa_log?
• SHR: I didn't expected the trigger to be fired when no data been changed.
• Dan Guzman: Lesson learned - triggers fire per statement regadless of the number of rows affected.

Answer 1

You are relying on the presence of rows in either inserted or deleted (or both). What happens when no rows are affected? The trigger still fires.

CREATE TABLE dbo.floob(a int);
INSERT dbo.floob(a) VALUES(1);
GO
CREATE TRIGGER dbo.TRfloob
ON dbo.floob
FOR INSERT, UPDATE, DELETE
AS
BEGIN
 IF NOT EXISTS (SELECT 1 FROM inserted) 
 AND NOT EXISTS (SELECT 1 FROM deleted)
 BEGIN
 PRINT 'Strange, unknown operation!';
 END
END
GO
UPDATE dbo.floob SET a = 2 WHERE a = 2;
GO

To avoid this kind of thing, typically people start their trigger with something like:

IF NOT EXISTS (SELECT 1 FROM inserted) 
 AND NOT EXISTS (SELECT 1 FROM deleted)
BEGIN
 RETURN;
END

Checking @@ROWCOUNT is popular too, but I find it far more brittle.

• SHR: so the trigger is fired even no data change?
• Hannah Vernon: correct. the trigger fires whenever the covered DML operations occur against the table, regardless of how many rows are affected, even if zero rows are affected.
• Aaron Bertrand: @SHR Yes, it will also fire for UPDATE dbo.floob SET a = a; - no data changes there, either, but all rows are technically affected.

Answer 2

Question: Should you be taking any action against the database if no changes are made? This seems like it will needlessly slow down your application, and is not best practice.

I would say here that a better solution would be to remove the unnecessary database transactions, if at all possible.

If this trigger is meant to audit in any way (which it looks like it is) then @Aaron Bertrand's answer will not log any updates to the audit table.

• RDFozz: Right - if no records were affected, then Aaron's answer will not add an update to the audit log. And, there may be times where determining beforehand if any records will actually be changed may be more effort than executing the command and not affecting any records.
• Aaron Bertrand: Of course my IF check doesn't have to simply yield a RETURN - you could still log the action inside that logic.