Re: Triggers Problem

In article <36pdv7$f9d_at_adam.cc.sunysb.edu> abukhari_at_adam.cc.sunysb.edu (Ali Bukhari) writes:
>
> Hello,
>
> I have run into a triggers problem and I was wondering if someone
> out here has any suggestions to make. Here's the scenario:
>
> I have two Oracle tables:
>
> Table 1 Definition:
> ------------------
>
> Create Table Level_One
> (Level1_Code char(2), constraint PK_LEVEL1 Primary Key
 (Level1_Code),
> Level1_Name char(25) not null);
>
>
> Table 2 Definition:
> ------------------
>
> Create Table Level_Two
> (Level1_Code char(2),
> Level2_Code char(2),
> Level2_Name chart(45)
> not null, constraint FK_LEVEL2_TO_LEVEL1 foreign key (LEVEL1_CODE)
> references Level_One(Level1_Code),
> constraint PK_LEVEL2 Primary Key (Level1_Code, Level2_Code) );
>
>
> And a Trigger:
>
>
> Trigger Definition:
> ------------------
>
> Create or Replace Trigger LEVEL1_AIR
> After Insert or Update of Level1_Code on Level_One -- Table
> For Each Row
> Begin
> if inserting then
> insert into Level_Two values (:new.Level1_Code, '00', 'N/A');
> end if;
> END;
>
>
> The problem is rather strange. I had first implemented the tables as
> LEVEL1 and LEVEL2 instead of Level_One and Level_Two. When I tried to
> test the trigger a "mutating table" error occurred on LEVEL1. I figured
> perhaps the names of the tables need to be a little more
 distinguishable, so
> I redefined the tables as Level_One and Level_two. The trigger worked.
> Yesterday I tried to test the trigger again, and again I ended up with
 the
> "mutating" table error. I changed the trigger to do the insertion on a
 test
> table called TIM as opposed to Level_Two and the problem went away.
>
> My question is does anybody have a clue as to what is going on. Is
 there
> a naming convention that I'm ignoring.
>
>
> I am running:
>
> Oracle7 Release 7.1.3.00
> Pl/Sql Release 2.1.3.00
>
>
> Please post any suggestions to abukhari_at_adam.sunysb.edu
>
>
> Thanks,
>
>
> Ali
>
>
>

Your problem is due to the fact that in table Level_Two (or LEVEL2) you are referencing the other table through a foreign key. When you try to insert a record in Level_Two in the Level_One trigger you therefore get the message about Level_One table mutating. If you eliminate the foreign key, your trigger will work fine.

You're welcome

Gilles Champagne
gch_at_logibec.com Received on Thu Oct 06 1994 - 14:15:12 CET