Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Creating a generic audit trigger with SQL 2005 CLR Expand / Collapse
Author
Message
Posted Sunday, October 21, 2012 7:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
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.
Post #1375227
Posted Sunday, October 21, 2012 7:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
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.
Post #1375228
Posted Sunday, October 21, 2012 7:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 35,594, Visits: 32,188
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375231
Posted Sunday, October 21, 2012 7:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 2:24 PM
Points: 69, Visits: 182
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.
Post #1375232
Posted Sunday, October 21, 2012 8:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 35,594, Visits: 32,188
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375237
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse