trigger to update one record on update of all the tables of database

[vaibhav_patel]

hi!

I have a big problem. If anyone can help.

I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.

I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.

But i don't know exactly how to do the coding for this?

Is there any other way to do this?

can DBCC help to retrieve this info?

Please advise me how to do this.

Thanks in advance.

Vaibhav

[rmiao]

Where is no such dbcc command.

[Rawhide]

Nor are there database wide triggers.

You have to have a trigger on every table.

Another alternative would be to use a log reader tool.

[MAK]

UNDocumented DBCC command

dbcc log(databasename,-1)

Look for End time for the same LSN and for the string.

LOP_MODIFY_ROW
LOP_INSERT_ROWS

Explore it, you will know.

[vaibhav_patel]

hi
Thanks for the reply.

Log reader may be useful to me.

But can i know the maximum time i mean last time database affected with any insert, delete or update.

is there any other way to get the max date thru this log.

or any other way to do this.

I want to get this details in the front end. and i have to print the last database access(insert,update,delete) time and date.

Thanks again.

[rmiao]

Then you may need kind of auditing table in the db.

[vaibhav_patel]

hi

Can you give me little more details about auditing table.

I don't know anything about the auditing table.

Thank you,

vaibhav

[rmiao]

Just record every transactions in that table for auditing perpose.

[vaibhav_patel]

I have one 128 tables in my database.

It is not possible to records each and every transaction.

[Rawhide]

It is possible. What you mean is that you don't want to do it.

[rmiao]

And you can record whatever you need in auditing table.