SQL 2005 XML Auditing
- Hi People,
I have been thinking of building a generic auditing system using
triggers and XML. to audit changes to a table. each change causes an
XML copy of the table row to be stored in the audit table so effectivly
this system could audit any table without knowing its structure.
eg:
People Table:
People
int Id,
varchar(200) Name,
int Age
The audit table would have:
Audit
datetime Date,
int Id,
char(1) DMLType (Trigger event: update, delete, insert),
XML OldRow (the values the row was before updated),
XML NewRow (the values after the row was updated)
Each time an update / insert / delete is executed the row would be
turned into XML using FOR XML and stored in the OldRow and the new
value in update statments would be put in the NewRow field.
So a change to the people table will result in an audit record results
like this:
Date: 01/01/2006
Id :1
DMLType : U
OldRow: <details Id="1" Name="Bob" Age="1"></details>
NewRow: <details Id="1" Name="Bob2" Age="43"></details>
How does that sound to people? any ideas on improvements? any major
issues with that? I have it working but not sure if its a good way to
go or not.
Appreciate your opinion!
Alex B
Ps I posted this in the SQL Server group and didn't get a response (that why it might look familiar
) -
This was removed by the editor as SPAM
-
Thanks Newbie!
Seems no one is interesting in throwing their two cents in for once
Thanks!
A
-
Doh sorry Site Owner thought your name was newbie!
Still any ideas anyone?
-
I've used this before and it works nicely. There's really little performance impact because you're not really going to need to parse the audit data all the time.
You might be able to get away with one Audit Table for all your tables:
CREATE TABLE DataAudit (
ID Int Identity(1,1) Primary Key,
TableName VarChar(128) Not Null,
AuditDate DateTime Not Null,
AuditType TinyInt Not Null, -- 0 = Select, 1 = Insert, 2 = Update, 3 = Delete
OriginalData [Text or Xml] Null,
CurrentData [Text or Xml] Null,
)
If you are using this for all tables you probably won't be able to store an "RecordID" column in the table because you may have tables with Composite Keys. It will be fine to store the ID columns in the Xml itself.
With SQL 2005 you can easily build a View off of the Xml data for each table. For example, say you are forced to show a representation of every "Audit" table. Well, all the audit data can be stored in one table, but you would build a view for each individual table (i.e. UsersAudit, OrdersAudit, etc.).
In SQL 2000, this should be done with a Stored Proc so that you can use OPENXML on the Columns of Xml Text.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply