February 2, 2009 at 7:53 am
Heyo,
I've developed a database to warehouse information about my companies applications, and everything is fine and dandy but right before I'm done the higher ups say they want any changes that users make to records to be audited and approved by a manager. I've investigated a few methods and I'm currently thinking about recording changes to an audit table as XML. This is awesome in a few ways:
1) It saves me from writing triggers and making audit tables for every table in the database
2) It's extensible and allows the application develoeprs to store whatever metadata they want (ie what screens the users see while they are making changes to particular tables)
3) Changes to several tables and records at once are easily managed
as well as others.
I'm thinking of storing the data that's being changed in an XML column on an audit table, and when it's approved converting it into a SQL query and running it.
I've also got this stumbling block:
insert into xml_table (data)
select * from example_table where example_table_pk='102' for xml auto
returns
Msg 6819, Level 16, State 1, Line 3
The FOR XML clause is not allowed in a INSERT statement.
Any help on this? I hope that all was clear.
---
Dlongnecker
February 2, 2009 at 8:12 am
This article has some data on that, and there's more in the discussion:
http://qa.sqlservercentral.com/articles/Auditing/63248/
What I've done since then is work out an audit trigger that looks something like this:
create trigger dbo.MyTable_Audit on dbo.MyTable
on update, delete
as
declare @XML XML;
select @XML =
(select
isnull(inserted.ID, deleted.ID) as ID,
nullif(deleted.MyCol1, inserted.MyCol1) as MyCol1,
nullif(deleted.MyCol2, inserted.MyCol2) as MyCol2,
--... and so on through the columns
from
inserted
full outer join deleted
on inserted.ID = deleted.ID
for XML raw, type);
insert into dbo.MyAuditTable (TableName, AuditDate, AuditXML)
select 'MyTable', getdate(), @XML;
(I typed that from RAM, which means Random Accuracy Memory in this case. So there may be errors in it, but the basic idea is in there.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2009 at 12:00 pm
dlongnecker (2/2/2009)
I've also got this stumbling block:
insert into xml_table (data)select * from example_table where example_table_pk='102' for xml auto
returns
Msg 6819, Level 16, State 1, Line 3The FOR XML clause is not allowed in a INSERT statement.
True, you cannot use the FOR XML clause on the SELECT clause of an INSERT..SELECT statement. However, you can use them on subqueries which means that, illogical as it may seem, you can do this instead:
INSERT Into xml_table (data)
Select (select * from example_table where example_table_pk='102' for xml auto)
go figure. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply