5

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;
Andrew
  • 3,632
  • 24
  • 64
  • 113

3 Answers3

7

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;
kinjelom
  • 6,105
  • 3
  • 35
  • 61
  • yes i make that change now its showing the actual error and the error is single row query returns more than one row. – Andrew Sep 22 '15 at 12:18
  • How many rows does return this query: Select KPI_FREQ_TIME_UNIT FROM KPI_DEFINITION ? – kinjelom Sep 22 '15 at 12:21
  • it returns more than one row . I have fixed this issue but now the issue is as i have more than 50 fields and if statement do i need to provide commit inside each if statement ? Because as soon as i declare PRAGMA AUTONOMOUS_TRANSACTION and does not commit then it gives an error as error saving changes to table "RATOR_MONITORING_CONFIGURATION"."KPI_DEFINITION": Row 1: ORA-06519: active autonomous transaction detected and rolled back – Andrew Sep 22 '15 at 12:23
  • Yes you have to commit it in this case... have you thought about using DBMS_JOB instead of this trigger? – kinjelom Sep 22 '15 at 12:31
  • ohh ok. I just add the commit at the bottom of my trigger before END and not inside each and every if statement. And its working but it will not cause any issue right ? – Andrew Sep 22 '15 at 12:33
  • This is completely separate transaction, do some experiments: update KPI_DEFINITION and rollback transaction... now select changes from FIELD_TRACKING (yes, they are still there). – kinjelom Sep 22 '15 at 12:41
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/90326/discussion-between-imagine-and-rahul). – kinjelom Sep 22 '15 at 12:42
  • i have messaged you in chat – Andrew Sep 22 '15 at 12:46
1

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).

losiu
  • 39
  • 2
0

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).

Ahmed MANSOUR
  • 2,369
  • 2
  • 27
  • 35