SQL Server create audit for every modified column

I want to create sql server audit for every modified column but i got error, this is my code :

BEGIN
  -- Type the SQL Here.
    DECLARE @event_type varchar(42)

    IF EXISTS(SELECT * FROM inserted)
        IF EXISTS(SELECT * FROM deleted)
            SELECT @event_type = 'U'
    ELSE
            SELECT @event_type = 'I'
    ELSE
        IF EXISTS(SELECT * FROM deleted)
            SELECT @event_type = 'D'
        ELSE
    --no rows affected - cannot determine event
    SELECT @event_type = 'UN'

    IF NEW.Code <> OLD.Code THEN  
        INSERT INTO CountryAuditTrail (Id, CountryId, Type, Field, OldValue, NewValue, CreatedAt, CreatedBy) 
        VALUES (NEWID(), Old.Id, @event_type, 'Code', Old.Code, New.Code, NEW.ModifiedAt, NEW.ModifiedBy);

    IF NEW.Name <> OLD.Name THEN  
        INSERT INTO CountryAuditTrail (Id, CountryId, Type, Field, OldValue, NewValue, CreatedAt, CreatedBy) 
        VALUES(NEWID(), Old.Id, @event_type, 'Name', Old.Name, New.Name, NEW.ModifiedAt, NEW.ModifiedBy);

    IF NEW.Status <> OLD.Status THEN  
        INSERT INTO CountryAuditTrail (Id, CountryId, Type, Field, OldValue, NewValue, CreatedAt, CreatedBy) 
        VALUES(NEWID(), Old.Id, @event_type, 'Status', Old.Status, New.Status, NEW.ModifiedAt, NEW.ModifiedBy);
END

I got this NEW and OLD from other stackoverflow solution, but it not work. and the error is :

Incorrect syntax near THEN

How to solve this?

So it can record to audit trail table for every column in insert or updated column


What I want to achieve is to record specified modified column using trigger.

In the Country table, I only specified to Code, Name and Status column

Insert trigger :

insert to table CountryAudit for every code, name and status

update trigger :

check if old.code and new.code different, if different then insert to CountryAudit

and other column too

728x90

2 Answers SQL Server create audit for every modified column

Your syntax wrong

Use this syntax

IF `condition` BEGIN
   statement;
END

4 months ago

Your code is sooo not the right way to write a trigger in SQL Server. You need to realize that inserted and deleted can have more than one row.

So, you need to use set operations.

If I follow the logic correctly, then you want something like this:

begin
    insert into CountryAuditTrail (Id, CountryId, Type, Field, OldValue, NewValue, CreatedAt, CreatedBy) 
        select newid(), coalesce(i.id, d.id),
               (case when i.id is null then 'D'
                     when d.id is null then 'I'
                     else 'U'
                end),
               v.col, v.oldvalue, v.newvalue,
               i.ModifiedAt, i.ModifiedBy
        from inserted i full join
             deleted d
             on i.id = d.id cross apply
             (values ('code', i.code, d.code),
                     ('name', i.name, d.name),
                     ('status', i.status, d.status)
             ) v(col, newvalue, oldvalue)
        where oldvalue <> newvalue or
              (oldvalue is null and newvalue is not null) or
              (oldvalue is not null and newvalue is null);
end;

4 months ago