Oracle_Trigger: Auto Update Of a Column Based Upon INSERT/UPDATE

Question

This might appear a simple query for most of you, but I am a beginner in Oracle DB. A table has been created with below script-

CREATE TABLE PLAN_TABLE
(
    PL_ID DECIMAL(10,0) PRIMARY KEY NOT NULL
   ,PL_NAME VARCHAR2(300) DEFAULT NULL
   ,UPDATED_TS TIMESTAMP DEFAULT SYSDATE NOT NULL
   ,DELETE_FLAG DECIMAL(10,0) DEFAULT 0 NOT NULL
);

The requirement is to have SYSDATE for UPDATED_TS for any new record inserted into the table and also in case when the DELETE_FLAG is updated to 1. Can it be done by trigger? The below trigger was created-

CREATE OR REPLACE TRIGGER PT_BEFORE_INSERT_TR
BEFORE INSERT ON PLAN_TABLE
FOR EACH ROW 
BEGIN 
SELECT SYSDATE INTO :new.UPDATED_TS FROM DUAL; 
dbms_output.put_line('Inserted');
END;
/

Below error was encountered while inserting record into the table- error: ORA-04091: table I60_SCH04.PLAN_TABLE is mutating, trigger/function may not see it

Can you please help in letting me know that where am I committing the mistake? Is there any better way to achieve the requirement based upon INSERT/UPDATE?

Answer 1

The actual error you get is due to the fact that you try to select from a table that you actually are changing. To prevent the issue there are a couple of methods, but in you case things are really simple.

SYSDATE is a function, that you could call directly inside PL/SQL block (which a trigger actually is) and use the value returned to update the set the column value

CREATE OR REPLACE TRIGGER PT_BEFORE_INSERT_TR
  BEFORE INSERT ON PLAN_TABLE
  FOR EACH ROW 
BEGIN 
  :new.UPDATED_TS := sysdate; 
  dbms_output.put_line('Inserted');
END;
/

OK, this covers the insert part.

For updating - once again, many options. One could be - change your trigger to BEFORE INSERT OR UPDATE ON PLAN_TABLE.

In this case whenever you issue update or insert - this trigger is fired for each row and updates the date column accordingly.

And of course you could use particular checks available in triggers, something like

IF INSERTING OR UPDATING('DELETE_FLAG') THEN
    ...
END IF;

and code in the logic you need.