6

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.

OldMcDonald
  • 594
  • 13
  • 36
  • What is `data1` and `data2`. Can you give example which will create/update the table you gave? – Utsav Mar 11 '16 at 10:08
  • Do you have flashback turned on? – MT0 Mar 11 '16 at 10:09
  • 2
    Basic strings or numbers, it could be anything. I'm going to edit my post to make it more clear, thanks. Flashback is turned off, but since I'm going to create a new database later on, I could turn it on. – OldMcDonald Mar 11 '16 at 10:09
  • Possible duplicate of [Select rows where column value has changed](http://stackoverflow.com/questions/11127461/select-rows-where-column-value-has-changed) – Abhijeet Mar 11 '16 at 10:59
  • 3
    You could write a trigger comparing each column each time a row changes, and write your result to an audit table. is it what you want? This will be a _fancy_ trigger to write (for any given table). Otherwise if you want to keep the record for modification time, along with previous values, you have to write a full datawarehouse. – J. Chomel Mar 11 '16 at 14:53

1 Answers1

2

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;
sulica
  • 111
  • 9
  • 1
    I found your first if statement conditions rather helpful because thats what I was doing as well – Fering Jul 22 '19 at 17:37