外键审计触发器 [英] Audit trigger for foreign key

问题描述

以下代码是我的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，则可以使用:

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.

这篇关于外键审计触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！