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

hi Bruce
Bruce R. Lewis wrote:
>
> 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...
>

 .. many skipped ...
> 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?

        You must write 3 triggers and 1 package.Package contains PL/SQL table variables (project_id etc) and 1 counter.BEFORE STATEMENT trigger clear package PL/SQL tables.AFTER EACH ROW trigger fill package PL/SQL table variables and increase counter.AFTER STATEMENT trigger scan PL/SQL tables and create records with PL/SQL variable values in child table.

         Konstantin V. Sartakov
                Kuzbassprombank
                       Kemerovo
                         Russia
           mailto:skv_at_kpbank.ru