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.

Answer 1

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

Answer 2

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