CODEWITHSUNDEEP
sqlsql-servert-sqltriggersaudit-logging
addsw
addsw

Reputation: 87

Display edited columns only using Audit Triggers in SQL database

I am using Microsoft SSMS.

I am trying to create an audit Trigger for my TestTable so that when a user edits or inserts any data, it will be recorded in this ChangeLogTable. The query below works and I am able to record any changes to the table.

However, whenever a user edits a row, it will record everything including the columns that are not edited. For example, if a user edits the TestDateTime, the TestStatus will appear in the ChangeLogTable even though there were no changes to it.

How do I change the query so that only the edited columns are recorded?

Create trigger test_Audit
on TestTable
after Insert, update
not for replication
as

Declare @operation char(10)

if exists(select * from deleted)
set @operation = 'Edit'
else
set @operation = 'Insert'


if UPDATE (TestDateTime)
insert ChangeLog
(TableName, ModifiedBy, ChangeLogDateTime, AuditAction, WorkScheduleID, ChangedColumn , OldValue, NewValue)
select
'TestTable', SUSER_SNAME(), GETDATE(), @operation, inserted.TestID,
'TestDateTime', Deleted.TestDateTime, Inserted.TestDateTime
from inserted
left outer join deleted
on inserted.TestID = deleted.TestID
and inserted.TestDateTime <> deleted.TestDateTime 


if UPDATE (TestStatus)
insert ChangeLog
(TableName, ModifiedBy, ChangeLogDateTime, AuditAction, WorkScheduleID, ChangedColumn, OldValue, NewValue)
select
'TestTable', SUSER_SNAME(), GETDATE(), @operation, inserted.TestID,
'TestStatus', Deleted.TestStatus, Inserted.TestStatus
from inserted
left outer join deleted
on inserted.TestID = deleted.TestID
and inserted.TestStatus<> deleted.TestStatus


if UPDATE (StaffID)
insert AuditLog
(TableName, ModifiedBy, AuditDateTime, AuditAction, ID, ChangedColumn, OldValue, NewValue)
select
'TestTable', SUSER_SNAME(), GETDATE(), @operation, inserted.TestID,
'StaffID', OSN.StaffName, NSN.StaffName
from inserted
left outer join deleted
on inserted.TestID= deleted.TestID
and inserted.StaffID <> deleted.StaffID
-- Fetch Staff Name
left outer join dbo.Staff OSN
on deleted.StaffID = OSN.StaffID
join dbo.Staff NSN
on inserted.StaffID = NSN.StaffID

Upvotes: 0

Views: 119

Answers (1)

Dale K
Dale K

Reputation: 27202

The problem is you have confused your join on clause with your where clause. Because you had for example inserted.TestStatus<> deleted.TestStatus as part of your on clause, you never matched a Deleted row if the data changed. Whereas what you wanted was to match the row on ID and then check whether the value had changed.

Note I've re-written the trigger using general best practices for clarity e.g.

  1. Using table aliases
  2. Making it set-based rather than procedural.
  3. Handling null values, because you can't use the <> operator when either side is null.
  4. Being consistent with what case (lower/upper/mixed) is used.
  5. Using semi-colons to terminate statements.
  6. Schema qualify your objects - I have assumed dbo.
  7. Forcing a convert to varchar for the datetime column as its better to convert explicitly rather than implicitly.
alter trigger dbo.test_Audit
on dbo.TestTable
after insert, update
not for replication
as
begin
  set nocount on;

  declare @operation char(10) = case when exists (select * from deleted) then 'Edit' else 'Insert' end;

  insert ChangeLog (TableName, ModifiedBy, ChangeLogDateTime, AuditAction, WorkScheduleID, ChangedColumn , OldValue, NewValue)
    select
      'TestTable', suser_sname(), getdate(), @operation, I.TestID,
      'TestDateTime', convert(varchar(21),D.TestDateTime), convert(varchar(21),I.TestDateTime)
    from inserted I
    left outer join deleted D on I.TestID = D.TestID
    where coalesce(I.TestDateTime,'1 jan 1900') <> coalesce(D.TestDateTime,'1 jan 1900')
    and update(TestDateTime)
    union all
    select
    'TestTable', suser_sname(), getdate(), @operation, I.TestID,
    'TestStatus', D.TestStatus, I.TestStatus
    from inserted I
    left outer join deleted D on I.TestID = D.TestID
    where coalesce(I.TestStatus,'') <> coalesce(D.TestStatus,'')
    and update(TestStatus)
end;

Upvotes: 1

Related Questions