CODEWITHSUNDEEP
sqloracle-databasetriggers
tomaytotomato
tomaytotomato

Reputation: 4028

Oracle Trigger for adding Old Val and New Val to audit table?

I am wanting to created an Oracle Trigger that will execute on Update on the table "User"

User

The Oracle trigger will then add rows to the following table "Audit"

Audit

Where it will record the changes of a specific field's Old value and New value and when it was changed.

My questions are:

Is an Oracle trigger going to work for this?

Here is my code so far (it is not functional as I do not know what to put as the argument for field)

Code

    CREATE OR REPLACE TRIGGER USER_UPDATE_TRG
BEFORE UPDATE OF USER_ID,Department, Privilege, Hashkey 
ON USER
BEGIN
  INSERT INTO AUDIT
  (
  USER_ID,
  FIELD_NAME,
  OLD_VAL,
  NEW_VAL,
  CHANGED_DATE
  )
  VALUES
  (
  USER_ID,
  <what do I put here for Field Name?,
  :old.Field,
  :new.Field,
  sysdate  
  )
END;

Upvotes: 1

Views: 2698

Answers (1)

Dba
Dba

Reputation: 6639

You can use UPDATING conditional predicate to identify the fieldname. 

CREATE OR REPLACE TRIGGER USER_UPDATE_TRG
BEFORE UPDATE OF USER_ID,Department, Privilege, Hashkey 
ON USER
FOR EACH ROW
BEGIN
     IF UPDATING ('USER_ID') THEN 
          INSERT INTO AUDIT
          (
          USER_ID,
          FIELD_NAME,
          OLD_VAL,
          NEW_VAL,
          CHANGED_DATE
          )
          VALUES
          (
          :new.USER_ID,
          'USER_ID',
          :old.USER_ID,
          :new.USER_ID,
          SYSDATE  
          );
     END IF;

     IF UPDATING ('DEPARTMENT') THEN
          .....
     END IF;

     IF UPDATING ('PRIVILEGE') THEN
          .....
     END IF;

     IF UPDATING ('HASHKEY') THEN
          .....
     END IF;
END;

In this case if multiple columns are updated, the trigger will insert multiple records in table audit.

Edited:

the trigger you have written is statement level trigger. the :NEW AND :OLD qualifiers will work in row level trigger for that you have to mention FOR EACH ROW.

I have added that in my code.

Upvotes: 2

Related Questions