need trigger on t1 to insert into t2 with column referencing t1

From: Bruce R. Lewis <brlewis_at_mit.edu>
Date: 1997/06/04
Message-ID: <nm9d8q22ih7.fsf@kindness.MIT.EDU>#1/1

It seems that to create a certain trigger I must remove a useful constraint. I'm hoping someone else has a better solution.

I have this table...

create table project_data(

    project_id integer CONSTRAINT pk_proj_id PRIMARY KEY

                          using index tablespace pdb_index,

    name varchar(255) CONSTRAINT proj_name_nn NOT NULL,     created_date date,
    [more deleted]...);

Certain projects are considered "informal". Anyone can create such a project because they can insert into this view...

create view project_data_informal

    as select * from project_data where process = 'NONE'     with check option;

There's another table which I can't grant public insert on:

create table team_membership(

    project_id integer CONSTRAINT fk_member_pid references

             project_data(project_id),
    name varchar(255),
    role varchar(30));

What I want is for a trigger to insert a "team leader" entry for a user whenever that user creates an informal project. This trigger almost does it:

create or replace trigger informal_project_leader

    AFTER INSERT ON project_data
    FOR EACH ROW
    BEGIN

      IF :new.process = 'NONE' THEN
        INSERT into team_membership
          VALUES(:new.project_id, USER, 'team leader');
      END IF;

    END;
/

The error I get when invoking this trigger is

ORA-04091: table PDB.PROJECT_DATA is mutating, trigger/function may not see it
ORA-06512: at "PDB.INFORMAL_PROJECT_LEADER", line 3
ORA-04088: error during execution of trigger 'PDB.INFORMAL_PROJECT_LEADER'

This error goes away if I insert into a different table (team_membership2) like team_membership but without the "references" constraint. I don't want to remove this constraint, but I want the trigger to work. It's obvious to a human that in this case there's no need to see project_data to realize the constraint is satisfied, but how can I make Oracle understand that?

Any other ideas would be helpful. Thanks.

-- 
Bruce R. Lewis			<brlewis_at_MIT.EDU>
MIT Information Systems		<URL:http://web.mit.edu/brlewis/www/>

Received on Wed Jun 04 1997 - 00:00:00 CDT