代码之家 › 专栏 › 技术社区 › Alexander Higgins

T-SQL触发器-审核列更改

audit triggers tsql sql-server sql

Alexander Higgins · 技术社区 · 3 年前

以下是我所拥有的:

Create Table Tbl_Audit
(
    AuditId int identity(1,1) not null,
    Tbl_Id int not null.
    Tbl_Old_ColumnValue varchar(255),
    Tbl_New_ColumnValue varchar(255)
)

GO

Create Trigger Tr_Tbl_ColumnChanged on Tbl
after insert, update
As
begin
    if(update(ColumnName))
        begin
            insert into tbl_audit
            (
                Tbl_Id,
                Tbl_Old_ColumnName,
                Tbl_New_ColumnName
            )
            select
                tbl.PKId,
                tbl.ColumnName,
                i.ColumnName,
            from
                Tbl tbl join 
                inserted i
                on tbl.PKId = i.PKId
            
end

我看到的是成千上万的例子 Tbl_Old_ColumnValue = Tbl_New_ColumnValue ,这不是我想要的。

我希望能跑:

select top 10 * from tbl_audit where Tbl_Old_ColumnValue !=Tbl_New_ColumnValue

为了得到实际更改的列的结果,我需要运行一个非常昂贵的查询:

select top 10 
old.AuditId,
old.Tbl_Old_ColumnValue,
new.Tbl_Old_ColumnValue  as [Tbl_New_ColumnValue] 
from tbl_audit [old]
join Tbl_Audit [new]
on [ol].Tbl_Id= [new].Tbl_Id and [old].AuditId != [new].AuditId
where [old].Tbl_Old_ColumnValue != [new].Tbl_Old_ColumnValue

结果:

AuditId Tbl_Id  Tbl_Old_ColumnValue Tbl_New_ColumnValue
10051       1       old_value           old_value
10052       1       new_value           new_value

但这并没有产生我所期望的结果:

AuditId Tbl_Id  Tbl_Old_ColumnValue Tbl_New_ColumnValue
10057       1   old_value           Some New Value

奇怪的是,如果我通过SSMS直接修改列,使用:

update Tbl set Tbl.ColumnValue = 'Some New Value'

试听Tbl\u Id Tbl\u Old\u ColumnValue Tbl\u New\u ColumnValue

我做错什么了?

另外,如何消除对行where的审核 update(ColumnName) 实际上是假的。即,ColumnName(即使已设置)在设置为上一个/旧值时不进行审核。

1 回复 | 直到 3 年前

Dale K 3 年前

update(ColumnName) 并不意味着该值已经更改,只是该列在insert/update中被涉及了,而且它将始终在insert中被涉及。您需要使用 inserted 和 deleted

insert into tbl_audit
(
    Tbl_Id,
    Tbl_Old_ColumnName,
    Tbl_New_ColumnName
)
select
    tbl.PKId,
    tbl.ColumnName,
    i.ColumnName,
from
    inserted i
    left join deleted d on d.PKId = i.PKId
    -- Insert d.PKId is null, there are no records in deleted
    where d.PKId is null
    -- Change from null to value
    or (i.ColumnName is null and d.ColumnName is not null)
    -- Change from value to null
    or (i.ColumnName is not null and d.ColumnName is null)
    -- Change in value
    or i.ColumnName <> d.ColumnName;

您可以使用 coalesce 以及一个合适的值,它永远不会出现在您的数据中。

这个 documentation 在这方面做得很好。

如果列不总是包含在更新中,则 更新(ColumnName) 测试仍然是值得做的,因为它加快了触发器的速度,并且触发器应该尽可能快。我个人很早就短路了。 if not update(ColumnName) return;

显然,您需要调整该逻辑来处理您正在审核的所有列。