Re: trigger cause "mutating" error

In article <01bfe599$52224d80$a0364f0f_at_bj210276>,   "junfan" <fanjun_at_bigfoot.com> wrote:
> Hi,
>
> I created 2 tables as follows:
>
> create table station(
> station_id char(6) primary key,
> name char(20),
> phone char(20)
> );
>
> create table cover_area(
> station_id char(6) primary key,
> area_code char(4)
> );
>
> create or replace trigger del_station
> after delete on station
> for each row
> begin
> delete from cover_area where station_id = :old.station_id;
> end;
>
> I then encountered error when I tried to delete rows from "station":
>
> > delete from station;
> ERROR at line 1:
> ORA-04091: table CCC.COVER_AREA is mutating, trigger/function may not
 see
> it
> ORA-06512: at "CCC.DEL_STATION", line 2
> ORA-04088: error during execution of trigger 'CCC.DROP_COVER_AREA'
>
> I was told the problem is due to "cover_area" is locked. but how can i
> overcome the error?
>
> Regards
>
>

If you want to enable referential integrity, then you'd better use foreign keys.
 create table station(
 station_id char(6) primary key,
 name char(20),
 phone char(20)
 );

 create table cover_area(
 station_id char(6),
 area_code char(4)
 constraint fk_cover_area_station_id foreign key(station_id) references station(station_id) on delete cascade
 );

 After
 >delete from station;
all records from cover_area will be deleted too. Hope this help
ivan_at_ivolmed.org.ru

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jul 04 2000 - 00:00:00 CDT