After_Update trigger logging

Question

USE [MY_DATABASE_NAME]
GO
/****** Object:  Trigger [dbo].[trg_After_Update]    Script Date: 16.12.2014 23:13:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_After_Update]
ON [dbo].[MY_TABLE_NAME] 
FOR UPDATE
AS
    declare  @FOR_DATE date;
    declare @WRITTEN_ON smalldatetime;
    declare @WRITTEN_BY_WHO NVARCHAR(50); 
    declare @REPORT nvarchar(max);
    declare @HANDLED bit; 
    declare @HANDLED_BY NVARCHAR(50);
    declare @HANDLED_WHEN datetime;
    declare @COMMENT nvarchar(max);
    declare @AUDIT_ACTION NVARCHAR(50);
    declare @AUDIT_TIMESTAMP smalldatetime;


    select @FOR_DATE     = i.FOR_DATE from inserted i;  
    select @WRITTEN_ON = i.WRITTEN_ON from inserted i;
    select @WRITTEN_BY_WHO = i.WRITTEN_BY_WHO from inserted i;
    select @REPORT         = i.REPORT from inserted i;
    select @HANDLED      = i.HANDLED from inserted i;
    select @HANDLED_BY = i.HANDLED_BY from inserted i;
    select @HANDLED_WHEN = i.HANDLED_WHEN from inserted i;
    select @ COMMENT       = i.COMMENT from inserted i;

                   if update(REPORT)
        set @audit_action='Report change';
        if update(COMMENT)
        set @audit_action='Comment change';
        if update(HANDLED)
        set @audit_action='Handled change';

                        insert into AUDIT_MY_TABLE_NAME


(FOR_DATE,WRITTEN_ON,WRITTEN_BY_WHO,REPORT,HANDLED,HANDLED_BY,HANDLED_WHEN,COMMENT,USER,AUDIT_ACTION,AUDIT_TIMESTAMP) 
    values

(@FOR_DATE,@WRITTEN_ON,@WRITTEN_BY_WHO,@REPORT,@HANDLED,@HANDLED_BY,@HANDLED_WHEN,@COMMENT,USER_NAME(USER_ID()),@audit_action,getdate());

This trigger works more or less as expected. It logs any change to the 3 monitored fields. However, a insert of a new record into this table 'MY_TABLE_NAME' fires the above trigger. Then when I go and see the auditing table 'AUDIT_MY_TABLE_NAME' I see that this new record has been added there. The only difference is that the 'audit_action' field is empty. This insertion into the auditing table is probably caused by another trigger that updates 2 fields in 'MY_TABLE_NAME' after_insert.

My question would be this : I kind of like the way this trigger works. The only additional feature I would like added to it is that the 'audit_action' reads 'New record' instead of displaying empty now. Mind you,I am not logging new records but since this After_Update trigger logs them anyway,why not…. So what must I change in this 'after_update' trigger so that when an insert of new record occurs I have 'audit_action' reading 'New record' in my auditing table?

Answer 1

Be very careful when you have triggers written for single updates, but don't prevent logging for batch updates. If you update 20 records in a batch, you're going to get 1 insert into your audit table, which will be a random one of the 20.

To meet your conditions without changing much, you can modify your trigger like so (this handles batch updates):

ALTER TRIGGER [dbo].[trg_After_Update]
ON [dbo].[MY_TABLE_NAME] 
FOR UPDATE
AS
    INSERT INTO AUDIT_MY_TABLE_NAME (FOR_DATE,WRITTEN_ON,WRITTEN_BY_WHO,REPORT,HANDLED,HANDLED_BY,HANDLED_WHEN,COMMENT,USER,AUDIT_ACTION,AUDIT_TIMESTAMP)
    SELECT i.FOR_DATE, i.WRITTEN_ON, i.WRITTEN_BY_WHO, i.REPORT, i.HANDLED, i.HANDLED_BY, i.HANDLED_WHEN, i.COMMENT, USER_NAME(USER_ID()),
        CASE  -- case statement is in reverse order to match your logic (bottom wins)
            WHEN i.HANDLED <> d.HANDLED THEN 'Handled Changed'
            WHEN i.COMMENT <> d.COMMENT THEN 'Comment Change'
            WHEN i.REPORT <> d.REPORT THEN 'Report Change'
            ELSE 'New Record'
        END,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d ON i.pk = d.pk  -- join on your Primary Key that doesn't change
END

But I'm wondering if what you think is going on is correct. When you update more than 1 of those 3 fields all of that logic is going to run. If more than 1 column of your 3 is being updated, the last one wins. My guess is that your "New Record" update is really a field outside of your 3 UPDATE columns being updated.

Here's another option and I'll let you choose what you think is best:

ALTER TRIGGER [dbo].[trg_After_Update]
ON [dbo].[MY_TABLE_NAME] 
FOR UPDATE
AS
    INSERT INTO AUDIT_MY_TABLE_NAME (FOR_DATE,WRITTEN_ON,WRITTEN_BY_WHO,REPORT,HANDLED,HANDLED_BY,HANDLED_WHEN,COMMENT,USER,AUDIT_ACTION,AUDIT_TIMESTAMP)
    SELECT i.FOR_DATE, i.WRITTEN_ON, i.WRITTEN_BY_WHO, i.REPORT, i.HANDLED, i.HANDLED_BY, i.HANDLED_WHEN, i.COMMENT, USER_NAME(USER_ID()),
        CASE WHEN i.pk IS NOT NULL AND d.pk IS NULL THEN 'New Record' ELSE
            CASE WHEN i.HANDLED <> d.HANDLED THEN 'Handled Changed. ' ELSE '' END + 
            CASE WHEN i.COMMENT <> d.COMMENT THEN 'Comment Change. ' ELSE '' END + 
            CASE WHEN i.REPORT <> d.REPORT THEN 'Report Change. ' ELSE '' END + 
            CASE WHEN i.HANDLED = d.HANDLED AND i.COMMENT = d.COMMENT AND i.REPORT = d.REPORT THEN 'Other Change.' ELSE '' END
        END,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d ON i.pk = d.pk  -- join on your Primary Key that doesn't change
END