sql - 如何确定更新触发器中更新了哪些字段

Question

更新:使用 Update_Columns() 并不是这个问题的答案，因为字段的顺序可能会发生变化，这会破坏触发器(Update_Columns 取决于列顺序)。

UPATE 2:我已经知道Deleted 和Inserted 表保存数据。问题是如何确定发生了什么变化，而不必对字段名称进行硬编码，因为字段名称可能会更改，或者可能会添加字段。

假设我有一个包含三个字段的表。

该行已存在，现在用户更新字段 1 和 2。

如何在更新触发器中确定更新了哪些字段以及更新前和更新后的值？

然后我想将这些记录到日志表中。如果有两个字段更新，那么历史表中应该会出现两行。

Table
Id  intField1  charField2  dateField3
7           3  Fred        1995-03-05

Updated To

7           3  Freddy      1995-05-06

History Table
_____________

Id  IdOfRowThatWasUpdated    BeforeValue    AfterValue (as string)
1                       7    Fred           Freddy
2                       7    1995-03-05     1995-05-06

我知道我可以使用已删除表来获取旧值，并使用插入表来获取新值。然而问题是如何动态地做到这一点。换句话说，实际的表有 50 列，我不想将 50 个字段硬编码到 SQL 语句中，而且如果字段发生变化，也不想担心保持 SQL 与表的变化。

格雷格

Answer 1

你可以使用我的 favorite XML-tricks 之一为此:

create trigger utr_Table1_update on Table1
after update, insert, delete
as
begin
    with cte_inserted as (
        select id, (select t.* for xml raw('row'), type) as data
        from inserted as t
    ), cte_deleted as (
        select id, (select t.* for xml raw('row'), type) as data
        from deleted as t
    ), cte_i as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_inserted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    ), cte_d as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_deleted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    )
    insert into Table1_History (ID, Name, OldValue, NewValue)
    select
        isnull(i.ID, d.ID) as ID,
        isnull(i.Name, d.Name) as Name,
        d.Value,
        i.Value
    from cte_i as i
        full outer join cte_d as d on d.ID = i.ID and d.Name = i.Name
    where
        not exists (select i.value intersect select d.value)

结束；

sql fiddle demo