Re: Triggers & Referencial integrity constraints

Antonio Galdo wrote:
>
> Hi everyone,
>
> I'm having some problems with triggers and referencial integrity
> constraints in the tables which fire and are modified by the triggers.

... skipped ...

> CREATE OR REPLACE TRIGGER hist_trigger
> AFTER
> UPDATE
> ON table_A
> FOR EACH ROW
> BEGIN
>
> INSERT INTO table_B
> (CODE,YEAR,ADATE)
> VALUES
> (:old.CODE,:old.YEAR,:old.ADATE);
>
> END;
... etc

Hi Antonio

Exception "Table mutating" raised only in "FOR EACH ROW" triggers.Statement
triggers(without FOR EACH ROW clause) working fine.You must write 1 package
and 3 triggers for solving your problem:

	1.You need packaged PL/SQL tables for pass-through field values:
          a) one PL/SQL table (tabtype table_a.field_x%TYPE) for one
field 
          (PL/SQL 2.2 and below);
          b) one table tabtype table_a%ROWTYPE for PL/SQL 2.3 and
above); 
          c) "row counter"/pointer variable for "multi-record"
statements.
	2.BEFORE statement trigger must clear PL/SQL table(s) and set
          "row conter"/pointer variable to 0.
	3.AFTER EACH ROW trigger must push :new field values into PL/SQL
table(s)           using "row counter"/pointer variable as index and
increase "row             counter"/pointer variable.
        4.AFTER statement trigger must insert records into table_b from 
          PL/SQL table(s).

I'm always use this algorythm, and it's working fine. Regards

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