Oracle Trigger - table name is mutating, trigger/function may not see it

Question

I am having an Oracle database with following Scenario(simplified):

Projects                      Params
-------------                 -----------
PROJ_ID     SCODE             PARAM_ID PARAM_TYPE PROJ_ID PARAM_VALUE
1000        123               5000       4614     1000    '00'
1001        124               5001       4610     1000    'Micro'
1002        123               5002       4614     1001    '02'
                              5003       4614     1002    '01'

This means, 3 Projects - Project 1000 has 2 different parameters and the second and third project has one parameter each.

Now I need to write a Trigger on the projects table which automatically inserts a new row in the params table with the highest value +1 of the parameter with type "4614" with the given SCODE.

INSERT INTO Projects VALUES (1003,123)

...should trigger the event

INSERT INTO Parameters VALUES (5004,4614,1003,'02')

Now I have 2 possibilities and both dont work:

If the trigger is declared as "BEFORE", I can't insert in the Parameters table because the foreign key constraint shows me an error that Project 1003 is not yet created. Doing a Commit inside a trigger is not possible.

If the trigger is declared as "AFTER", I get an error

ORA-04091: table name is mutating, trigger/function may not see it

because I am accessing the table which is triggered right now.

There must be any solution to this problem. Any help is appreciated!

//Edit

My Trigger:

CREATE OR REPLACE TRIGGER PROJ_ARI_TRIGGER
AFTER INSERT
ON PROJECTS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_param VARCHAR2(10);
BEGIN
      v_param := get_next_param_val(:new.SCODE);
      INSERT INTO Parameters(<<sequence>>,4614,:new.PROJ_ID,v_param);
END PROJ_ARI_TRIGGER;

The Function get_next_param_val just does the select of the projects table and returns the right parameter value.

You could change the foreign key so that it is deferrable; that way, it won't be checked until you commit. Bear in mind that this could cause problems if you're doing bulk updates - you won't get errors until the very end of the transaction. Alternatively, instead of inserting directly into the table, do it via a stored procedure. That way, you can then control the logic and flow correctly, without having to do any fancy workarounds. — Boneist, May 26 '15 at 15:29
@Boneist Unfortunately I cant change the Constraint of the database system. Why would putting the INSERT Statement into a stored procedure help? The Content of my trigger trigger is like v_new_param := calculate_param(); insert into Parameters values(....,new.proj_id,v_new_param). — Deniz Gezgin, May 26 '15 at 15:50
it would be better if you were to edit your question to add the script to create your trigger. — Boneist, May 26 '15 at 15:52
I don't understand the algorithm you're trying to describe. You're trying to get a `param_value` value, correct? Why would you get a value of `02`? That's not the max()+1 of the current data which would seem to be `03` if we assume that the string values will always be convertable to a number. If you're doing a max()+1, you understand that you'll generate duplicates if you allow more than one user in the system, right? — Justin Cave, May 26 '15 at 16:13
[You may find that a compound trigger will be your friend here](http://stackoverflow.com/questions/29489951/oracle-trigger-after-insert-or-delete). — Bob Jarvis - Слава Україні, May 27 '15 at 01:22

score 0 · Answer 1 · answered May 26 '15 at 16:18

Darned mutating tables! Time for a package.

CREATE OR REPLACE PACKAGE no_mutation_pkg
AS
   procedure set_val(pVal number);
   function get_val return number;
END;
/

CREATE OR REPLACE PACKAGE BODY no_mutation_pkg
AS
   g_val number;
   procedure set_val(pVal number)
   IS
     BEGIN
       g_val := pVAl;
   END;
   function get_val return number
   IS BEGIN
      return g_val;
   end;
   END;

And then you create a BEFORE insert trigger that calculates the value and stores it in the package with set_val, and the AFTER insert trigger retrieves the value with GET_VAL and does the insert.

EDIT: Per justin's comment on duplicates, you'd definitely need to consider that. Frankly, the data architecture is generally suspect when you need to jump through such hoops

good catch. Thanks! I'm typing on-the-fly today as I'm away from the office. — Michael Broughton, May 26 '15 at 17:12

score 0 · Answer 2 · answered May 27 '15 at 09:41

0

I found a solution for my problem!

Using a Compound trigger, I can first get the maximum Value in the SCODE and calculate the param value (with before each row)

and then insert the value in the param table (with after each row)

Details: Compound Trigger Example

Thank you @Bob Jarvis

answered May 27 '15 at 09:41

Deniz Gezgin

53
1
7

Per Justin`s comment above, if this is a multi-user system you might want to test to ensure that you aren`t going to get duplicates if two people perform a similar insert. e.g. for the dataset above have one person insert project 1003 with scode 123, then have another insert project 1004 with scode 123, then have both do their commit. – Michael Broughton May 28 '15 at 13:16

Oracle Trigger - table name is mutating, trigger/function may not see it

2 Answers2