Audit changes in table using stored procedure

Question

Post reply

Audit changes in table using stored procedure

lsalih

SSCertifiable

Points: 6143
More actions
June 5, 2013 at 8:45 am

#294907

I would like to ask for help with creating a stored procedure to generate below information:
1- A time stampt for record creation in a table, a time stamp shows when was updated, and which user last the record. I know that by querying sys.dm_db_index_usage_stats you can get the information, but I like to have a store procedure created in master database to run it whenever any changes are made in any database.
Thanks,
Lava

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

lsalih (6/5/2013)
I know that by querying sys.dm_db_index_usage_stats you can get the information

You will not get all this information by querying this table.

A better option would be to create a trigger on your table and then do some coding inside the trigger to audit the changes.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lowell SSC Guru Points: 323504 More actions · Answer 2

lsalih (6/5/2013)
I would like to ask for help with creating a stored procedure to generate below information:
1- A time stampt for record creation in a table, a time stamp shows when was updated, and which user last the record. I know that by querying sys.dm_db_index_usage_stats you can get the information, but I like to have a store procedure created in master database to run it whenever any changes are made in any database.
Thanks,
Lava

sys.dm_db_index_usage_stats only gives you when the index was last used, it does not tell you, on a per record basis, when things where changed.

you'd want to either enable CDC for the table in question, or add columns to the table like CREATEDDate (with a default of getdate()) and ModifiedDate, and have a trigger set the ModifiedDate value from the INSERTED virtual tableinside the trigger.

you mention any changes, so it seems maybe cdc on every table might be an option.

then your stored procedure could query the cdc tables for the latest changes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lsalih SSCertifiable Points: 6143 More actions · Answer 3

As I mentioned we need to have something for all databases in case we get a request about changes were made to a table. I appreciate it if you provide me with a code to do so.

lsalih SSCertifiable Points: 6143 More actions · Answer 4

Lowell -

After doing more research, I think I will go with your recommendation to enable CDC and write a store procedure to get the information users need...

Many thanks to you.

Lava

lsalih SSCertifiable Points: 6143 More actions · Answer 5

Lowell -

I did setup cdc and it is working the way I needed, I used http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/ as a base to get started. Once again, thank you.

Lava