Tracing the time when a value was updated in a table

Question

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,

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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