Jeff Moden (11/11/2012)
@Gus,Although I certainly appreciate the self-healing nature of XML to automatically capture column additions deletions to the table, full row auditing is already expensive from a storage standpoint. It seems that the bloat of XML tags would make that much worse. Considering that a table might never suffer a structure change in it's lifetime, is it really worth using XML for such a thing?
If the table is static enough for it, or if you want to play around with some moderately complex DDL triggers (which can rewrite the DML trigger for you if the attached object changes), then doing the XML trigger can actually be an even better solution.
Just change the Select in the trigger to look like this:
NullIf(deleted.MyCol1, inserted.MyCol1) as Col1, NullIf(deleted.MyCol2, inserted.MyCol2)
FROM inserted
FULL OUTER JOIN deleted
ON inserted.ID = deleted.ID
Keep the For XML, Type on there. XML defaults NULL-value columns out of the dataset completely. If you update a single column, that's the only one that goes into the audit log. If you update 2 columns, they go in. If you update everything, it all goes in. Unlike the Update() function in triggers, this method will actually correctly handle a column that's set to the same value it already has, by treating it as unchanged.
On tables that routinely get narrow updates, this usually ends up taking a lot less storage space than column-matched log tables (where the log table has the same columns as the table being logged), and is a lot faster than name-value log tables (the ones that insert ColumnName, NewValue type data into a vertical log). Also avoids the name-value log overhead on reconstituting the data, or accidentally getting two transactions crossed up.
I've tested that method on real data, and it pretty routinely works out better than any other active logging solution I've seen. Add the DDL trigger trick to it, where any column changes result in the trigger automatically being updated to match the new columns, and you have a logging system that can really have all the advantages and no real drawbacks.
- 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