How to audit column of a table in sql server 2005

Question

Post reply

How to audit column of a table in sql server 2005

Rishi-853338

SSC Enthusiast

Points: 150
More actions
December 10, 2008 at 9:42 am

#126402

We need to audit some of the columns of a database. Is there any method apart from the trigger? Also, if any sample trigger is available, please reply with it.

Viewing 8 posts - 1 through 7 (of 7 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

You can modify the stored procedures that you use to access the data to use an audit table.

Rishi-853338 SSC Enthusiast Points: 150 More actions · Answer 2

We have vendor softwares to access the tables. I want to store the insert, update and delete events in a different table, so that they can be reviewed later.

Steve Jones - SSC Editor SSC Guru Points: 736544 More actions · Answer 3

Do you know how to write a trigger?

If you do, then an audit trigger just inserts changed data into some table. There are articles here on the site (search audit) and lots available. We are happy to help, but it appears you haven't done any work on this yourself

steveb. SSC-Forever Points: 46733 More actions · Answer 4

Rishi (12/10/2008)
We have vendor softwares to access the tables. I want to store the insert, update and delete events in a different table, so that they can be reviewed later.

then you will have to write a trigger or invest in some log-reading software

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Steve Jones - Editor (12/10/2008)
Do you know how to write a trigger?
If you do, then an audit trigger just inserts changed data into some table. There are articles here on the site (search audit) and lots available. We are happy to help, but it appears you haven't done any work on this yourself

I think you are being unfair in this case Steve. Poster is asking for a method other than trigger so I assume poster knows how to implement audit via triggers.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Rishi-853338 SSC Enthusiast Points: 150 More actions · Answer 6

Paul,

I am aware that it can be done using trigger, but dont know how to write a trigger as I have never done it before and I am weak in T-SQL.

steveb. SSC-Forever Points: 46733 More actions · Answer 7

Rishi (12/10/2008)
Paul,
I am aware that it can be done using trigger, but dont know how to write a trigger as I have never done it before and I am weak in T-SQL.

have a look at this article that explains how to do a simple insert audit trigger

http://www.sqlservercentral.com/articles/Administering/auditingyoursqlserverpart1/917/%5B/url%5D