CODEWITHSUNDEEP
oracle-databasetriggers
Krishnat Molawade
Krishnat Molawade

Reputation: 306

New value is same as old value if not passed for update in oracle before update trigger

I have created a before insert/update trigger as follows

CREATE OR REPLACE TRIGGER "AUDIT_TABLE_TRIG" BEFORE
  INSERT OR UPDATE ON AUDIT_TABLE FOR EACH ROW BEGIN :NEW.LAST_MODIFIED_DATE:=SYSDATE;
  IF :new.LAST_MODIFIED_BY = NULL THEN
    :NEW.LAST_MODIFIED_BY :=SYS_CONTEXT('USERENV','OS_USER');
  END IF;
END;

Requirement was to update last_modified_by value same as logged in user instead of OS user, if last_modified_by is null then take OS user id. When i was updating this table from UI it worked fine. Last modified by value is coming as logged in user id (as it was getting passed in update statement). But when i updated some other column in AUDIT_TABLE from sql developer, :new.last_modified_by is never coming null instead it has value same as :old.last_modified_by

Que1- I have handled this scenario in not so good way as

 IF :new.LAST_MODIFIED_BY = NULL || :OLD.LAST_MODIFIED_BY !=SYS_CONTEXT('USERENV','OS_USER') THEN
            :NEW.LAST_MODIFIED_BY :=SYS_CONTEXT('USERENV','OS_USER');
 END IF;

Is there other good way I can handle this?

Que2: I came across this scenario and i got surprised. is it a standard behavior for oracle triggers that new variables have old values if they are not changed while updating?

Upvotes: 2

Views: 3306

Answers (1)

hflzh
hflzh

Reputation: 644

Try using the UPDATING predicate, that would probably meet your requirement.

CREATE OR REPLACE TRIGGER "AUDIT_TABLE_TRIG" BEFORE
  INSERT OR UPDATE ON AUDIT_TABLE FOR EACH ROW BEGIN :NEW.LAST_MODIFIED_DATE:=SYSDATE;
  IF NOT UPDATING('LAST_MODIFIED_BY') THEN
    :NEW.LAST_MODIFIED_BY :=SYS_CONTEXT('USERENV','OS_USER');
  END IF;
END;

Upvotes: 1

Related Questions