6

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?

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

2 Answers 2

8

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.

3
  • so the trigger is fired even no data change?
    – SHR
    Jul 16, 2018 at 16:36
  • 4
    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.
    – Hannah Vernon
    Jul 16, 2018 at 16:39
  • @SHR Yes, it will also fire for UPDATE dbo.floob SET a = a; - no data changes there, either, but all rows are technically affected. Jul 16, 2018 at 17:00
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.

2
  • 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.
    – RDFozz
    Jul 16, 2018 at 22:43
  • Of course my IF check doesn't have to simply yield a RETURN - you could still log the action inside that logic. Oct 2, 2018 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.