无法获取在sql中的历史表中更新的列名

Question

我在sql server中有一个表，其中有很多列，每次都可以更新。现在，我想要一个触发器，它可以将我创建的、在主表中更新的值插入到历史表中。历史表如下所示:NewValue(用于存储在主表中更新的列的名称，id (主表中的行的id)，OldValue(更新的旧值)，VarName(新值)，date(更新的日期)。我正在使用此代码，但我希望触发器自动检测更改的列，并将它们的名称存储在VarName列中。请记住，在主表中的一行中可以更新多个字段，因此如果更新了3个字段，历史表将添加3行，其中包含主表中各自的列名及其旧值和新值的更新。在UPDATE AS BEGIN之后在dbo.MainTable上创建触发器dbo.UpdateOnMainTable

    INSERT [dbo].[Historical] (VarName, ID, OldValue, NewValue, Date)
        SELECT ===> the code here
        FROM inserted i
        INNER JOIN deleted d
            ON i.ID = d.ID

正如我所解释的，我想要一个填充我的历史表的代码。

Answer 1

首先，您的结构要求数据类型与历史表中的列兼容。

其次，您需要取消旋转，然后从那里开始工作：

with ic as (
      select i.id, v.*
      from inserted i cross apply
           (values ('col1', i.col1), ('col2', i.col2), ('col3', i.col3)
           ) v(col, val)
     ),
     dc as (
      select d.id, v.*
      from deleted d cross apply
           (values ('col1', d.col1), ('col2', idcol2), ('col3', d.col3)
           ) v(col, val)
     )
insert into historical (varname, id, oldvalue, newvalue, date)
    select ic.col, ic.id, dc.val, ic.val, getdate
    from ic join
         dc
         on ic.id = dc.id and
            ic.col = dc.col and
            (ic.val <> dc.val or
             ic.val is null and dc.val is not null or
             ic.val is not null and dc.val is null
            );

请注意，如果列具有不同的类型，则需要在values()语句中对它们进行强制转换(假设为字符串)。