外键的审计触发器

2021-11-30 00:00:00 tsql sql-server triggers audit

下面的代码是我的 Branch 表的审计触发器.它能够记录 Branch 表中的任何编辑或插入更改.

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.

但是，我有一个到 BranchZone 表的外键 BranchZoneID.我想这样做，以便记录对此外键的任何更改，并显示 BranchZone 表中的 BranchZoneName 而不是 BranchZoneID.

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.

我尝试使用代码为外键属性 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.

BranchZoneID 的触发器不起作用.我需要改变什么?

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;

推荐答案

您刚刚对您的加入感到困惑... 正确加入并按预期工作.请记住，当使用表别名时，您只能从那时起使用别名来引用表，例如I 而不是 Inserted.

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.

还假设 BranchZoneID 是一个 int，您需要将它coalesce 与未使用的 int 例如0 不是空字符串.

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);

如果 BranchZoneID 是一个 uniqueidentifer，你会使用:

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')

当使用coalesce以这种方式比较值时，您需要选择一个对数据类型有效但在数据中无效的值.

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.

相关文章