sql-server - 将 Inserted 和 Deleted 行转换为 Xml 以供审计

Question

我有以下触发器:

ALTER trigger tr_mytable_audit on mytable after insert, update, delete
as
SELECT *
FROM
    (SELECT *
     FROM inserted
     UNION
     SELECT *  
     FROM deleted) as Rows
FOR XML RAW, ELEMENTS
GO

目前插入和删除的输出如下:

<row>
  <id>1</id>
  <column1>test</column1>
  <column2>62</column2>
</row>

虽然更新是:

<row>
  <id>1</id>
  <column1>test</column1>
  <column2>62</column2>
</row>
<row>
  <id>1</id>
  <column1>changed</column1>
  <column2>62</column2>
</row>

是否可以将 xml 转换成这样:

--Inserts
<row>
  <id old="" new="1"></id>
  <column1 old="" new="test"></column1>
  <column2 old="" new="62"></column2>
</row>

--Deletes
<row>
  <id old="1" new=""></id>
  <column1 old="changed" new=""></column1>
  <column2 old="62" new=""></column2>
</row>

--Updates
<row>
  <id old="1" new="1"></id>
  <column1 old="test" new="changed"></column1>
  <column2 old="62" new="62"></column2>
</row>

更好的是，对于更新，是否可以省略未更改的列，以便 xml 只是:

<row>
  <column1 old="test" new="changed"></column1>
</row>

编辑

我想出了这个 SQL:

ALTER trigger tr_mytable_audit on mytable after insert, update, delete
as
select d.id as 'id/old', i.id as 'id/new',
       d.column1 as 'column1/old', i.column1 as 'column1/new',
       d.column2 as 'column2/old', i.column2 as 'column2/new'
from inserted as i
  full join deleted as d
    on i.id = d.id
for xml path('row'), root('rows')

但是，对于更新，我还有未更改的列。是否可以通过改进上述SQL来过滤掉未更改的列？

Answer 1

尝试加入 inserted 和 deleted 并使用 XML PATH 对要生成的 XML 结构有更多的控制。像这样:

SELECT 
      ID = COALESCE(I.ID, D.ID)
    , Col1 = (SELECT [@old] = D.Col1, [@new] = I.Col1 
              WHERE D.Col1 <> I.Col1 OR D.Col1 IS NULL OR I.Col1 IS NULL)
    , Col2 = (SELECT [@old] = D.Col2, [@new] = I.Col2 
              WHERE D.Col2 <> I.Col2 OR D.Col2 IS NULL OR I.Col2 IS NULL)
FROM INSERTED AS I
    FULL JOIN DELETED AS D
        ON I.ID = D.ID
FOR XML PATH('row'), ROOT('rows')