Re: Trigger generating mutation errors

In article <8hlub4$oge$1_at_news.duke.edu>,   "PBM" <morri027_at_mc.duke.edu> wrote:
> We have a trigger that is generating the following error when we try
 to
> delete a record from a table called SubDepartmentSplit:
>
> Line 1: Offset 16: ORA-04091: table AFM.SUBDEPARTMENTSPLIT is
> mutating, trigger/function may not see it
> ORA-06512: at "AFM.SDS_BDR", line 14
> ORA-04088: error during execution of trigger 'AFM.SDS_BDR'
>
> Here is the code for the trigger:
>
> declare
> integrity_error exception;
> errno integer;
> errmsg char(200);
> num_sds_records integer;
>
> /* If we're deleting the last SUBDEPARTMENTSPLIT record, reset the */
> /* need_to_assign flag on the ROOMDEPARTMENTSPLIT record. */
> begin
> select count(*) into num_sds_records from SubDepartmentSplit where
> room_department_split_id = :old.room_department_split_id;
> if num_sds_records = 0 then
> update RoomDepartmentSplit set need_to_assign = 'Y' where
> room_department_split_id = :old.room_department_split_id;
> end if;
> exception
> when integrity_error then raise_application_error(errno,
 errmsg);
> end;
>
> Am I getting an error because I am trying to select from the table
 that I am
> trying to delete from?
>
> Thanks,
> PBM
>
>

row level triggers in general cannot read the table they are defined on (there is one case when they can -- an insert row level trigger BEFORE insert for each row, on a single insert into T values statement)...

See
http://osi.oracle.com/~tkyte/Mutate/index.html for how to defer your processing until an AFTER trigger.

basically, you'll have a row trigger save the :old.room_department_split_id in an array and process them all in the AFTER trigger.

BTW: the exception integrity_error, since you never throw it, will never be raised -- whats its purpose? since errno and errmsg are never set -- its good the exception block would never get entered into but why have it?

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.