Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL database trigger
What is the proper way for implementing audit triggers. I found many examples but I am still not sure what is the best way to implement. I need to track changes on few tables. Basically I need to keep track of everything. I want it nice and clean.
Posted 25-Apr-13 19:55pm
Comments
gvprabu at 26-Apr-13 3:55am
   
you have to track only table changes (DDL) or DML things also.
If any other object s like SP, Functions, views also u need to track the changes.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Refer this link to understand how to implement[Audit Trail].
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

The "proper" way to make triggers really depends on your database schema, your application, your need to audit etc etc .... for example *we* currently audit approximately 150 "sensitive" items across 20 tables ... we don't "care" about the rest.
 
This link has discussions about several potential solutions http://stackoverflow.com/questions/1962398/creating-audit-triggers-in-sql-server[^]
 
When you say you need to track "everything" do you mean update, deletions, insertions ... (answer should probably be yes to the last two)... when you "audit" an update ... do you actually need to know what was updated or just the fact that the record was updated.
When you have defined clearly what your requirements are only then can you really determine the "proper" way.
 
An alternative that has been used by certain solution providers is to have columns on each table that show when they were created (and who by) and when they were last updated (and who by) ... this is very simply implemented using table triggers without having to purchase 3rd party tools ... and also simplifies the subsequent detailed audit requirements
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If you just need to make triggers for a few tables I suggest installing some tool like ApexSQL Audit[^] Use it in trial mode to make audit triggers and then examine how it is done. Than just make your triggers. Nice and clean Smile | :)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,569
1 Sergey Alexandrovich Kryukov 6,168
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 28 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100