Tracing the time when a value was updated in a table

Hi Friends,

Can anybody please, let me know if there is a way to find out the exact time, when a table was updated.I mean when a column was updated. Please, help.

Thanks,

From stackoverflow

• add a trigger, when the inserted (changed) data matches your condition do your notification (insert into log table, etc.)

• I like to keep a LastUpdatedTimeStamp column in my tables where I need that kind of tracking.

  If you're asking about tracing a change that has already been made, you might be able to analyze transaction logs. I don't know how one would go about doing that though.

  KM : this doesn't tell you that the column in question was changed, just something on the row.

  Ian Jacobs : D'oh... When did that second sentence show up? :-)

• Best practice is Update Trigger on that table that will call a generic procedure where you can pass parameters like description field name and primary key of updated record as well the timestamp.

• You could add a trigger on update of the relevant column. This will only be triggered if the relevant column actually was changed. Example below is for Oracle.

  CREATE OR REPLACE TRIGGER my_trigger
  BEFORE UPDATE
  OF my_column
  ON my_table
  BEGIN
     :new.my_column_updated_at := SYSDATE;
  END;
  

  my_column_updated_at will then track when my_column was changed. You may also want to add a trigger on insert to set an initial value of my_column_updated_at.

  If you need to find out when, for any row in the table, the column was updated, you could issue the following SQL:

  SELECT MAX(my_column_updated_at) FROM my_table