How to find out when a column was changed in Oracle DB?

Question

I want to be able to see when a specific column was changed. To clarify, I don't want to see when a row was changed. I was able to do that with ROWDEPENDENCIES and triggers with a timestamp. With ROWDEPENDICIES I can see when the whole row was modified last but I specifically want to find out when the a specific column was changed.

To clarify, I want the following table:

id   Name       Name_modified_on    Salary   Salary_modified_on
 1   Johnson    11.03.16 10:54:27   5000     11.03.16 10:51:27  

How do I do that? Thanks. PS: I'm running Oracle 11g.

Answer 1

Try this code:

1. Create a table:

   CREATE TABLE TEST
   (
     ID                  VARCHAR2(2000 BYTE),
     NAME                VARCHAR2(2000 BYTE),
     NAME_MODIFIED_ON    DATE                      DEFAULT sysdate,
     SALARY              NUMBER(10),
     SALARY_MODIFIED_ON  DATE                      DEFAULT sysdate
   );
   

2. Create a DB trigger:

   DECLARE
   /******************************************************************************
      NAME:       COLUMN_UPDATE_TRG
      PURPOSE:    
   
      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        4.8.2016     Tomaz Kristan    1. Created this trigger.
   
      NOTES:
   
      Automatically available Auto Replace Keywords:
         Object Name:     COLUMN_UPDATE_TRG
         Sysdate:         4.8.2016
         Date and Time:   4.8.2016, 14:33:39, and 4.8.2016 14:33:39
         Username:         (set in TOAD Options, Proc Templates)
         Table Name:      Z_TEST (set in the "New PL/SQL Object" dialog)
         Trigger Options:  (set in the "New PL/SQL Object" dialog)
   ******************************************************************************/
   BEGIN
   
   IF UPDATING THEN
   
       IF ( :new.name is not null AND :new.name != :old.name ) THEN
            :new.name_modified_on := sysdate;
       END IF;   
   
       IF ( :new.salary is not null AND :new.salary != :old.salary ) THEN
            :new.salary_modified_on := sysdate;
       END IF;           
   
   END IF;
   
      EXCEPTION
        WHEN OTHERS THEN
          -- Consider logging the error and then re-raise
          RAISE;
   END COLUMN_UPDATE_TRG;