sql - 在我的表触发器中记录更改的更有效方法

Question

目前我对每个表的触发器对于表中的每个字段都是这样的:

ALTER TRIGGER [dbo].[trg_Statement] ON [dbo].[tbl_Statement]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
    INSERT INTO tbl_ChangeLog(TableName, ID, FieldName, OldValue, NewValue)
       SELECT  
          'Statement', CU.id, 'id', deleted.id,inserted.id
       FROM
          tbl_Statement CU
       LEFT JOIN 
          inserted on CU.id = inserted.id
       LEFT JOIN 
          deleted on CU.id = deleted.id
       WHERE
          (inserted.id is not null or deleted.id is not null)
          AND IsNull(inserted.id,'') <> IsNull(deleted.id,'')

    INSERT INTO tbl_ChangeLog(TableName, ID, FieldName, OldValue, NewValue)
       SELECT 
          'Statement', CU.id, 'idAccount', deleted.idAccount,inserted.idAccount
       FROM
          tbl_Statement CU
       LEFT JOIN 
          inserted on CU.id = inserted.id
       LEFT JOIN 
          deleted on CU.id = deleted.id
       WHERE
          (inserted.id is not null or deleted.id is not null)
          AND IsNull(inserted.idAccount,'') <> IsNull(deleted.idAccount,'')

    INSERT INTO tbl_ChangeLog(TableName, ID, FieldName, OldValue, NewValue)
       SELECT 
          'Statement', CU.id, 'OpeningBalance', deleted.OpeningBalance,inserted.OpeningBalance
       FROM
          tbl_Statement CU
       LEFT JOIN 
          inserted on CU.id = inserted.id
       LEFT JOIN 
          deleted on CU.id = deleted.id
       WHERE
          (inserted.id is not null or deleted.id is not null)
          AND IsNull(inserted.OpeningBalance,'') <> IsNull(deleted.OpeningBalance,'')
   ...

然而，这非常昂贵，尤其是当有很多字段时，任何人都可以提出一种更有效的方法来跟踪我们的变更日志的更改吗？

Answer 1

我同意 Madison 的观点，您的 AUDIT 表中包含整个记录的单行将更有用且性能更高。基表越宽，这一点就越真实。在你的情况下:

CREATE TRIGGER IUD_Statement_Audit
ON dbo.tbl_Statement AFTER INSERT, UPDATE, DELETE
AS BEGIN

  IF (@@rowCount = 0) RETURN;

  SET NOCOUNT ON;

  INSERT INTO dbo.tbl_Statement_AUDIT
    (id, idAccount, OpeningBalance, insertedOrDeleted, modTime, modId)
  SELECT id, idAccount, OpeningBalance, 'D', GETDATE(), USER_NAME() FROM DELETED
  UNION ALL
  SELECT id, idAccount, OpeningBalance, 'I', GETDATE(), USER_NAME() FROM Inserted

END

这种方法有很多好处，包括:轻松加入 id 列以查看所有表的所有更改和触发器成为复制/粘贴练习。一般来说，RDBMS 在宽表(一条记录包含所有列)和较少记录的情况下会比瘦 [?] 表(每个列更改一条记录)表现得更好。您当前的方法将为每条插入、删除和更新的记录创建三个记录。

可以通过按 modTime desc, insertedOrDeleted desc 对审计表进行排序并执行反向操作来回滚到过去的点...回滚到你想要的位置。