主页 / dba / 问题 / 326202
Accepted
Lando_
Asked: 2023-04-20 13:58:19 +0800 CST

审计触发器仅在特定列更改时跟踪，并且仅记录已更改列的旧值和新值

  • 772

我有一个包含 75 列的表，并且需要跟踪其中大约 15 列，以便在这 15 列中的任何列中的任何数据发生变化（仅更新，无插入或删除）时注销旧值和新值。例外情况是我们只想跟踪这 15 个列中更新的列。如果旧值和新值均为 NULL 或两者相同，我们只想将 NULL 记录到这些列的审计表中。

多列可以为空，并且是整数、小数和 nvarchar 数据类型的组合。

这一切都在 Azure SQL 数据库上运行。

我写了一个初始的更新后触发器，它有一个插入语句到一个有 30 列的审计表中，一个“旧”和“新”列用于我们希望跟踪的所有 15 个列。当旧值和新值都不为 NULL 时，这一切都很好，但如果旧值或新值之一恰好为 NULL，我们就会丢失数据。

为了解决这个问题，我开始为每一列编写 case 语句，以及一堆 where 子句，但我觉得这不是正确的方法。基本上，有 15 种不同的语句变体，例如：

CREATE OR ALTER TRIGGER [dbo].[trg_Stuff_Audit]
ON [dbo].[Stuff]
AFTER UPDATE
AS
BEGIN

insert into dbo.audit (id, OldDecimalValue, NewDecimalValue)

select i.id,

case when d.DecimalValue is null and i.DecimalValue is not null then d.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then d.DecimalValue
when d.DecimalValue <> i.DecimalValue then d.DecimalValue
else NULL end as OldDecimalValue,

case when d.DecimalValue is null and i.DecimalValue is not null then i.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then i.DecimalValue
when d.DecimalValue <> i.DecimalValue then i.DecimalValue
else NULL end as NewDecimalValue

from inserted i inner join deleted d on i.id = d.id

where d.DecimalValue is null and i.DecimalValue is not null
OR d.DecimalValue is not null and i.DecimalValue is null
OR d.DecimalValue <> i.DecimalValue

END;

我觉得必须有更好的方法来解决这个问题，我是走在正确的道路上还是需要改变方向？

trigger

3 个回答

  1. 你也许可以使 dbo.stuff 成为临时/版本表。每行都有一个开始和结束时间，旧行将被放入历史表中。

    时间表

    • 2

  2. 我建议像其他答案一样沿着使用时态表的路线走下去。

    但是，如果您仍然想坚持使用触发器，则可以通过取消透视值并一次性进行比较来简化这一过程。

    换句话说：不要再次存储整行，只存储一对和OldValue列NewValue，每行代表每行对一列的一次更改。

    CREATE OR ALTER TRIGGER [dbo].[trg_Stuff_Audit]
ON [dbo].[Stuff]
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    RETURN;

INSERT dbo.audit (id, ColumnName, OldDecimalValue, NewDecimalValue)
SELECT
  ISNULL(i.id, d.id),
  v.ColumnName
  v.OldValue,
  v.NewValue
FROM inserted i
JOIN deleted d on i.id = d.id
CROSS APPLY (
    SELECT *
    FROM (VALUES
      ('decimalValue1', d.decimalValue1, i.decimalValue1),
      ('decimalValue2', d.decimalValue2, i.decimalValue2),
      ('decimalValue3', d.decimalValue3, i.decimalValue3)
    ) v(ColumnName, OldValue, NewValue)
    WHERE v.OldValue IS DISTINCT FROM v.NewValue
) v;

    如果您有不同的数据类型，那么您需要转换为sql_variant

    ...
    FROM (VALUES
      ('decimalValue1', CAST(d.decimalValue1 AS sql_variant), CAST(i.decimalValue1 AS sql_variant)),
      ('string2',       d.string2,       i.string2),
      ('int3',          d.int3,          i.int3)
    ) v(ColumnName, OldValue, NewValue)
...

    在早于 SQL Server 2022 的版本上，您需要更改WHERE

        WHERE v.OldValue <> v.NewValue
       OR v.OldValue IS NULL and v.NewValue IS NOT NULL
       OR v.OldValue IS NOT NULL and v.NewValue IS NULL

    请注意，我已经使用AFTER INSERT, UPDATE, DELETEorder 来完全跟踪对表的所有更改。另外，请注意 unpivot在连接后工作，它不会多次扫描插入和删除的表

    • 1
  3. Best Answer

    我认为您最好的方法是修改现有触发器以正确处理旧值或新值的 NULL。由于您在云中，您可以使用 IS DISTINCT FROM CharlieFace 在您的 WHERE 子句中引用。

    WHERE I.DecimalValue IS DISTINCT FROM D.DecimalValue
    OR I.DecimalValue2 IS DISTINCT FROM D.DecimalValue2
    /** REPEAT **/

    这可能是您获得良好答案的最快途径。

    就我个人而言，我倾向于远离时态表，因为它们确实会跟踪所有内容，并且像你一样我有非常具体的要求并且遵守具体要求会使速度更快。

    这让我也回避像 Charlieface 推荐的更复杂的解决方案。当它一次是一两行时，这并不重要，但是当您一次可能有数百行以上的行供触发器扫描时，INSERTED 和 DELETED 表是堆的事实意味着吞吐量将受很大的苦。快速而肮脏地插入一个可能很少被读取的审计表要好得多，即使以干净的方式重新构建事件需要更多的工作。

    如果你想在旧的和新的相同时继续显示 NULL，那么你可以这样做..

    SELECT I.ID 
    , DecimalValue_NEW = ISNULL(NULLIF(I.DecimalValue, D.DecimalValue), NULLIF(D.DecimalValue, I.DecimalValue))
    , DecimalValue_OLD = ISNULL(NULLIF(D.DecimalValue, I.DecimalValue), NULLIF(I.DecimalValue, D.DecimalValue))
    , DecimalValue2_NEW = ISNULL(NULLIF(I.DecimalValue2, D.DecimalValue2), NULLIF(D.DecimalValue2, I.DecimalValue2))
    , DecimalValue2_OLD = ISNULL(NULLIF(D.DecimalValue2, I.DecimalValue2), NULLIF(I.DecimalValue2, D.DecimalValue2))
FROM INSERTED AS I
    INNER JOIN DELETED AS D ON D.ID = I.ID 
WHERE I.DecimalValue IS DISTINCT FROM D.DecimalValue
    OR I.DecimalValue2 IS DISTINCT FROM D.DecimalValue2

    有关我从何处获得 ISNULL(NULLIF(A,B), NULLIF(B,A)) 构造...

    我对触发器的规则是快速完成工作，如果你能帮助它就不要尝试分析数据并离开......并使操作防弹，以便它只在你需要它抛出异常时抛出异常例外。

    • 0

相关问题