CODEWITHSUNDEEP
plsql
Victor Zakharov
Victor Zakharov

Reputation: 26424

Create a trigger to update time stamp on inserted or updated records?

I am trying to create an Oracle trigger that would update entry_stamp column (type=DATE) on every inserted or updated record for a certain table. Here is my script:

CREATE OR REPLACE TRIGGER mytable_entry_stamp 
AFTER INSERT OR UPDATE ON mytable FOR EACH ROW
BEGIN :NEW.entry_stamp := SYSDATE; END;

I am getting this error:

ORA-04084: cannot change NEW values for this trigger type

Upvotes: 0

Views: 6055

Answers (1)

Victor Zakharov
Victor Zakharov

Reputation: 26424

From Oracle/PLSQL: AFTER UPDATE Trigger:

  • You can not update the :NEW values.
  • You can not update the :OLD values.

It appears that Oracle cannot update a record inside AFTER trigger, unlike MSSQL. So it is reserved for logging/audit purposes, i.e. a record can be inserted or updated in another table. After I converted this trigger to BEFORE, it worked flawlessly.

Upvotes: 2

Related Questions