我的数据库中有很多表.这些表中的每一个都有触发器在更新或删除时触发,以将更改记录到AuditLog表. AuditLog表包含以下内容:
Id(PK,int,not null)
Action(nchar(1),not null)
ActionDate(datetime,not null)
ActionUser(nvarchar(100),not null)
AuditData(XML(.),not null)
在我的触发器中,我正在做如下事情:
DECLARE @auditBody XML
SET @auditBody = (select * from deleted as Root for xml auto,elements)
insert into dbo.AuditLog
(Action,ActionDate,ActionUser,AuditData)
select Case
When I.Id is not null then 'U'
Else 'D'
End as Action,getdate() as ActionDate,suser_name() as ActionUser,@auditBody as AuditData
From
deleted D Left Join
inserted I on D.Id = I.Id
这很好,但是,我想做的是向tablename的根元素添加一个属性,以便AuditData xml看起来像:
132
Ryan
...
有没有办法通过select for … for xml语句来实现这一点?