Creating a generic audit trigger with SQL 2005 CLR

Question

Creating a generic audit trigger with SQL 2005 CLR

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

David Ziffer SSCommitted Points: 1513 More actions · Answer 1

I never did follow up further on this. However the subject of comprehensive auditing is covered in another article series on this web site, entitled "writing nearly codeless apps", I believe.

David Ziffer SSCommitted Points: 1513 More actions · Answer 2

Sorry if this is a multiple post, but I tried responding once and it didn't post. I never did follow up further on this single-audit-table approach to auditing, since it is of somewhat limited use (reconstructing data using this mechanism would be pretty complex). I did write another article series on this site, however, entitled "Writing Nearly Codeless Apps", which discusses a much more practical and comprehensive auditing mechnism that allows you to easily recall every row of every table of an application as of a given date.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

David Ziffer (10/21/2012)
Sorry if this is a multiple post, but I tried responding once and it didn't post. I never did follow up further on this single-audit-table approach to auditing, since it is of somewhat limited use (reconstructing data using this mechanism would be pretty complex). I did write another article series on this site, however, entitled "Writing Nearly Codeless Apps", which discusses a much more practical and comprehensive auditing mechnism that allows you to easily recall every row of every table of an application as of a given date.

Nice, short and sweet artcle. However, between the more than doubling the size of the database just for the inserts and saving a full row even if only one column got updated, I have to say that you must have a huge amount of disk space available. Backups and restores will suffer, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Ziffer SSCommitted Points: 1513 More actions · Answer 4

Everything has its price. The flip side: I have never seen any other application(s) anywhere, other than ones written by me using this system, that are capable of returning you (via the app's ordinary user interface) to any desired moment in the past.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

David Ziffer (10/21/2012)
Everything has its price. The flip side: I have never seen any other application(s) anywhere, other than ones written by me using this system, that are capable of returning you (via the app's ordinary user interface) to any desired moment in the past.

Gosh... TYPE 2 SCDs are used for such a thing all the time. The only difference between what they do and what you're suggesting is that you store the whole rows in a separate table rather than in the original table. The EAV solution is also capable of doing such a thing albeit with a bit more effort.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)