SQL Server 2005 如何建立一个审计表来记录更新的列名？

Question

鉴于此表定义，

create table herb.app (appId int identity primary key
 , application varchar(15) unique
 , customerName varchar(35),LoanProtectionInsurance bit 
 , State varchar(3),Address varchar(50),LoanAmt money
 ,addedBy varchar(7) not null,AddedDt smalldatetime default getdate())

我相信更改将是最小的，通常只有一个字段，并且非常稀疏。

所以我创建了这个表：

create table herb.appAudit(appAuditId int primary key
 , field varchar(20), oldValue varchar(50),ChangedBy varchar(7) not null,AddedDt smalldatetime default getdate())

如何在触发器中获取更改后的值的列名来存储它？ 我知道如何通过连接已删除的表来获取值。

Answer 1

使用插入和删除表。 Nigel Rivett 使用这些表编写了一个很棒的通用审计跟踪触发器。 这是相当复杂的 SQL 代码，但它强调了一些将信息组合在一起的非常酷的方法，一旦您理解了它们，您就可以使用他的想法作为灵感来创建自定义解决方案，或者您可以只使用他的脚本。

以下是关于表的重要思想：

• 在插入时，inserted 保存插入的值，deleted 为空。
• 更新时，inserted 保存新值，deleted 保存旧值。
• 删除时，deleted 保存已删除的值，inserted 为空。
• 插入和删除的表的结构（如果不为空）与目标表相同。
• 您可以从系统表中确定列名称并对其进行迭代，如 Nigel 的代码所示。

<代码> <代码>

    if exists (select * from inserted)
        if exists (select * from deleted)
            -- this is an update
            ...
        else
            -- this is an insert
            ...
    else
        -- this is a delete
        ...

-- For updates to a specific field
SELECT d.[MyField] AS OldValue, i.[MyField] AS NewValue, system_user AS User
FROM inserted i
INNER JOIN deleted d ON i.[MyPrimaryKeyField] = d.[MyPrimaryKeyField]

-- For your table
SELECT d.CustomerName AS OldValue, i.CustomerName AS NewValue, system_user AS User
FROM inserted i
INNER JOIN deleted d ON i.appId = d.appId

>

Answer 2

如果您确实需要以对您的业务至关重要的方式进行这种审核，请查看 SQL Server 2008 的更改数据捕获功能。 仅此功能就可以证明升级成本是合理的。

Answer 3

对于您想要跟踪的每个字段，类似这样的内容

    if UPDATE(Track_ID)
begin

insert into [log].DataChanges
(
    dcColumnName,
    dcID,
    dcDataBefore,
    dcDataAfter,
    dcDateChanged,
    dcUser,
    dcTableName
)
select
'Track_ID',
d.Data_ID,
coalesce(d.Track_ID,-666),
coalesce(i.Track_ID,-666),
getdate(),
@user,
@table
from inserted i
    join deleted d on i.Data_ID=d.Data_ID
        and coalesce(d.Track_ID,-666)<>coalesce(i.Track_ID,-666)

end

“Track_ID”是字段的名称，d.Data_ID 是您跟踪的表的主键。 @user 是进行更改的用户，@table 是您跟踪更改的表，以防您在同一日志表中跟踪多个表

Answer 4

这是我的快速而肮脏的审计表解决方案。 （来自http://freachable.net/2010/09/29/QuickAndDirtySQLAuditTable.aspx)

CREATE TABLE audit(
  [on] datetime not null default getutcdate(),
  [by] varchar(255) not null default system_user+','+AppName(),
  was xml null,
  [is] xml null
)

CREATE TRIGGER mytable_audit ON mytable for insert, update, delete as
INSERT audit(was,[is]) values(
  (select * from deleted as [mytable] for xml auto,type),
  (select * from inserted as [mytable] for xml auto,type)
)