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 anticipated this when reading through :-)

> 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.

The problem has nothing to do with the distinguishability of the names; the problem is that you are putting a value into a Foreign Key column before the referenced column has, in some sense, been completely inserted with the same value. I don't know why it worked for you sometimes and not others; I couldn't get your example to work once (I always got the mutating table error). Perhaps it's a timing thing (anyone from Oracle?).

> 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.

Ah, but does TIM refer to that column in table Level_One?

> My question is does anybody have a clue as to what is going on. Is there
> a naming convention that I'm ignoring.

Yes; it's not naming that's the problem. You need to do the insertions into Level_Two After the insert statement has finished doing its job. This works:

Create or Replace Trigger LEVEL1_AIR

   After Insert or Update of Level1_Code on Level_One -- Table    Begin

      if inserting then
         insert into Level_Two select level1_code, '00', 'N/A' from level_one a
            where not exists (select null from level_two
                              where level1_code = a.level1_code);
      end if;

> Oracle7 Release 7.1.3.00
> Pl/Sql Release 2.1.3.00
 

> Please post any suggestions to abukhari_at_adam.sunysb.edu

Ok; copy sent there.

> Thanks,

You're welcome.

> Ali

Hank Robinson
Oracle DBA
University of Warwick Received on Fri Oct 07 1994 - 20:58:49 CET