Audit trigger for foreign key

Question

The code below is an Audit trigger for my Branch Table. It is able to record any edit or insert changes from the Branch table.

However, I have a foreign key BranchZoneID to the BranchZone table. I want to make it so that any changes to this foreign key will be recorded, and that it will display the BranchZoneName from the BranchZone table instead of the BranchZoneID.

I tried playing around with the code to create a trigger for the foreign key attribute BranchZoneID. However, I am unable to create a working trigger for it.

The trigger for the BranchZoneID is not working. What do I need to change?

create trigger Branch_Audit
on dbo.Branch
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 AuditLog
(TableName, ModifiedBy, AuditDateTime, AuditAction, ID, ChangedColumn, OldValue, NewValue)
    select
      'Branch', suser_sname(), getdate(), @operation, I.BranchZoneID,
      'BranchName', convert(varchar(21),D.BranchName), convert(varchar(21),I.BranchName)
    from inserted I
    left outer join deleted D on I.BranchID = D.BranchID
    where coalesce(I.BranchName,'') <> coalesce(D.BranchName,'')
    and update(BranchName)
    union all

    select
    'Branch', suser_sname(), getdate(), @operation, I.BranchID,
    'BranchAddress', D.BranchAddress, I.BranchAddress
    from inserted I
    left outer join deleted D on I.BranchID = D.BranchID
    where coalesce(I.BranchAddress,'') <> coalesce(D.BranchAddress,'')
    and update(BranchAddress)
    union all


    select
    'Branch', suser_sname(), getdate(), @operation, I.BranchID,
    'BranchGeoLocationLat', D.BranchGeoLocationLat, I.BranchGeoLocationLat
    from inserted I
    left outer join deleted D on I.BranchID = D.BranchID
    where coalesce(I.BranchGeoLocationLat,'') <> coalesce(D.BranchGeoLocationLat,'')
    and update(BranchGeoLocationLat)
    union all


    select
    'Branch', suser_sname(), getdate(), @operation, I.BranchID,
    'BranchGeoLocationLong', D.BranchGeoLocationLong, I.BranchGeoLocationLong
    from inserted I
    left outer join deleted D on I.BranchID = D.BranchID
    where coalesce(I.BranchGeoLocationLong,'') <> coalesce(D.BranchGeoLocationLong,'')
    and update(BranchGeoLocationLong)
    union all

    select
    'Branch', SUSER_SNAME(), GETDATE(), @operation, I.BranchID,
    'BranchZoneID', OWD.BranchZoneName, NWD.BranchZoneName
    from inserted I
    left outer join deleted D on I.BranchID = D.BranchID
    where coalesce(I.BranchZoneID,'') <> coalesce(D.BranchZoneID,'')
    and update(BranchZoneID)
    -- Fetch Branch Zone Name
    on deleted.BranchZoneID = OWD.BranchZoneID
    join dbo.BranchZone NWD
    on inserted.BranchZoneID = NWD.BranchZoneID

end;

Answer 1

You just got confused with your join... join correctly and it works as intended. And remember when using a table alias you can only reference the table using the alias from then on e.g. I instead of Inserted.

Also assuming BranchZoneID is an int you need to coalesce it to an unused int e.g. 0 not an empty string.

select
    'Branch', suser_sname(), getdate(), @operation, I.BranchID
    , 'BranchZoneID', OWD.BranchZoneName, NWD.BranchZoneName
from inserted I
left outer join deleted D on I.BranchID = D.BranchID

-- Fetch Branch Zone Names
left join dbo.BranchZone OWD on OWD.BranchZoneID = D.BranchZoneID
left join dbo.BranchZone NWD on NWD.BranchZoneID = I.BranchZoneID

where coalesce(I.BranchZoneID,0) <> coalesce(D.BranchZoneID,0)
and update(BranchZoneID);

And if BranchZoneID is a uniqueidentifer you would use:

where coalesce(I.BranchZoneID,'00000000-0000-0000-0000-000000000000') <> coalesce(D.BranchZoneID,'00000000-0000-0000-0000-000000000000')

When using coalesce to compare values in this way, you need to choose a value which is valid for the datatype but which is invalid in your data.