Post reply

Want to know date in time when table was updated

  • July 12, 2006 at 5:07 am

    #113720

    Hi all

    My table was updated by my client and i know which new record is added but i want to know on which date and at what time it was updated. I don't have any date field in that table. Is it possible to know on which date that record was entered in my table...

    Thanks

  • July 12, 2006 at 5:18 am

    #648663

    This information is not recorded by default. The only way would be if you have backups, and you can work it out by trawling through them.

     

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • July 13, 2006 at 1:23 pm

    #649087

    if you want to log EVERYTHING, you can turn on C2 auditing. eek!

     

    You may want to just create an audit table with column values  like user_name, host_name, @@spid, DBCC INPUTBUFFER(@@SPID)/::fn_Get_sql(), GETDATE(), and any other data that you would find useful to locate rows in the data table (like invoice number in an invoices table).

    Then make an UPDATE trigger on the table that they are updating that will write the above data to the audit table.

    This way every time an update happens on the table, an audit record of who, when, what, and from where is written to the audit table.

    You can do the same thing with inserts and deletes, should you want to log those as well, and this method should affect the data that your customer is accessing ( or the program they are using to access it with). Ideally the application should control this logging, but we sometimes dont have that control.

     

Viewing 3 posts - 1 through 2 (of 2 total)

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