Re: Triggers Problem

Ali Bukhari (abukhari_at_adam.cc.sunysb.edu) wrote:

: 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

Just a guess but the mutating table error is because a trigger is trying to modify a table where the change began. The source of the 'confusion'/bug might be to do with the constrainsts that you have between the two tables. Received on Thu Oct 06 1994 - 10:54:08 CET