Sql-server – SQL Server: trigger for insert update and delete caught when no data been changed

sql servertrigger

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?

Best Answer

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.

Related Question