Trigger generating mutation errors

From: PBM <morri027_at_mc.duke.edu>
Date: 2000/06/07
Message-ID: <8hlub4$oge$1@news.duke.edu>#1/1

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 Received on Wed Jun 07 2000 - 00:00:00 CDT