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?

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.

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.