CODEWITHSUNDEEP
oracle-databasetriggersinsert
Angelina
Angelina

Reputation: 2265

create trigger on INSERT IN table for specific values

I am trying to crate a trigger in oracle 12c that will execute procedure if inserted values contain specific values.

What I am trying to do is after certain tables (EVENTS, MARKS, STAGE) are all refreshed, only then I want the trigger to run REFRESH_MVS(); procedure.

And they are stored in COUNTS table after they are refreshed. So I am checking if new INSERT in COUNTS has keyword: EVENTS, MARKS, STAGE.

Is this the way to do it?

CREATE or replace TRIGGER MV_REFRESH
AFTER INSERT ON COUNTS
  FOR EACH ROW

DECLARE
   MODEL_NAME varchar2(20);

BEGIN
   select MODEL INTO MODEL_NAME from COUNTS;

  IF(MODEL_NAME = 'EVENTS' AND MODEL_NAME = 'MARKS' AND MODEL_NAME = 'STAGE')
  THEN
      REFRESH_MVS();           
  END IF;     
END;

After compiling it successfully if I run INSERT:

INSERT INTO COUNTS 
values ('EVENTS',   '11658495', '0.11', '17-MAR-14',    '17-MAR-14');

It throws error:

Error starting at line 3 in command:
INSERT INTO COUNTS
values ('EVENTS',   '11658495', '0.11', '17-MAR-17',    '17-MAR-17')
Error report:
SQL Error: ORA-04091: table COUNTS is mutating, trigger/function may not see it
ORA-06512: at "MV_REFRESH", line 5
ORA-04088: error during execution of trigger 'MV_REFRESH'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Upvotes: 0

Views: 860

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

It does not appear that what you are trying to do is sensible. If you can explain the business problem you are trying to solve, we can probably assist you in coming up with a more appropriate technical implementation.

In general, in a row-level trigger on a table, you cannot query the table in question. It does not appear that you have any need to query the table in this trigger, though. My guess is that you just need to use the :new.model_name. But if that's the case, the logic doesn't make sense-- it is, of course, impossible for a single attribute to have three different values at the same time.

IF(:NEW.MODEL_NAME = 'EVENTS' AND 
   :NEW.MODEL_NAME = 'MARKS' AND 
   :NEW.MODEL_NAME = 'STAGE')
THEN
  REFRESH_MVS();           
END IF;

Perhaps you meant OR rather than AND

IF(:NEW.MODEL_NAME = 'EVENTS' OR
   :NEW.MODEL_NAME = 'MARKS' OR 
   :NEW.MODEL_NAME = 'STAGE')
THEN
  REFRESH_MVS();           
END IF;

which could be simplified

IF(:NEW.MODEL_NAME IN( 'EVENTS', 'MARKS', 'STAGE') )
THEN
  REFRESH_MVS();           
END IF;

Now, you'll also get the mutating table exception if the refresh_mvs tries to query the counts table. If the current implementation tries to query counts, you would need to change the procedure to accept as parameters whatever data it requires from the current row that is being inserted.

If refresh_mvs is actually refreshing materialized views, that implies that it is at least doing implicit commits. That would create further issues because you cannot commit in a trigger (unless the trigger is defined as an autonomous transaction which would not be appropriate here).

Upvotes: 2

Related Questions