CODEWITHSUNDEEP
oracle-databasetriggerssql-insert
Marcus
Marcus

Reputation: 3869

ORA-04091: table is mutating, trigger/function may not see it error during execution of oracle trigger

I have below trigger in which for FIELD_NAME field i want to insert value into FIELD_TRACKING table as 'Deactivation time of KPI in case of Downtime(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)'. The bracket part in this string value comes from KPI_FREQ_TIME_UNIT field of KPI_DEFINITION table. So below is the trigger i have wrritten for this. The trigger compile without any error. But when i try to change the DNTM_REAC_AFTER_HRS field from the KPI_DEFINITION table then i am getting error ORA-04091: table RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION is mutating, trigger/function may not see it ORA-04088: error during execution of trigger 'RATOR_MONITORING_CONFIGURATION.TRG_TRK_KPI_DEFINITION'.

create or replace TRIGGER RATOR_MONITORING_CONFIGURATION."TRG_TRK_KPI_DEFINITION" AFTER UPDATE ON RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION
      FOR EACH ROW

    IF NOT  :old.DNTM_REAC_AFTER_HRS=:new.DNTM_REAC_AFTER_HRS THEN
        INSERT INTO RATOR_MONITORING_CONFIGURATION.FIELD_TRACKING  (FIELD_TRACKING_ID,TABLE_NAME,TABLE_ID, FIELD_NAME,FIELD_OLD_VALUE,FIELD_NEW_VALUE,USER_ID, TIMESTAMP, FIELD_TRACKING_COMMENTS)
        VALUES (FIELD_TRACKING_SEQ.NEXTVAL,'KPI_DEFINITION',:new.KPI_DEF_ID,'Deactivation time of KPI in case of Downtime'|| '(' || to_char((Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION)) || ')',to_char(:old.DNTM_REAC_AFTER_HRS),to_char( :new.DNTM_REAC_AFTER_HRS),:new.LAST_UPDATED_BY,:new.LAST_UPDATED_DATE, decode(:new.KPI_ACTIVE_DOWNTIME,'N','This KPI has been reactivated on end of a downtime.',''));
      END IF;

    END;

Upvotes: 6

Views: 47869

Answers (3)

losiu
losiu

Reputation: 59

The reason for this error is: Triggers with "for each row" is "row level" trigger and it can only see that row.

The solution is to use autonomous transaction (so that the action DML is at another transaction, not bound by the trigger limitation), or you can use statement level trigger (remove "for each row" at declaration).

Upvotes: 2

kinjelom
kinjelom

Reputation: 6450

Trigger cannot read the table (Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION), that changes... you can access the value in this way: :new.KPI_FREQ_TIME_UNIT. More info: http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm

In other cases you can try to do it in autonomous transaction:

create or replace TRIGGER RATOR_MONITORING_CONFIGURATION."TRG_TRK_KPI_DEFINITION" 
AFTER UPDATE ON RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- ...
  COMMIT; -- don't forget it!!!
END;

Upvotes: 9

Ahmed MANSOUR
Ahmed MANSOUR

Reputation: 2559

Since you need informations from the table for which you created your trigger for(Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION), you can get the KPI_FREQ_TIME_UNIT from the reference :NEW as it represents the new row (:NEW.KPI_FREQ_TIME_UNIT).

Upvotes: 0

Related Questions