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?
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