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

Yes. Since you are deleting records from the SubDepartmentSplit table, and have yet to complete the action (due to the trigger) the select becomes part of the delete transaction, that is for each row deleted the trigger fires and tries to select from the SubDepartmentSplit table while a delete is still pending; Oracle considers this mutation (data is changing as it is being selected) and won't allow this to occur. The trigger errors out and the transaction is rolled back. You might try converting this trigger to a procedure, passing in the room_department_split_id, commiting the delete transaction then executing the trigger text. The table should not be mutating at that point and the subsequent update of the RoomDepartmentSplit table should complete as expected.

David Fitzjarrell
Oracle Certified DBA

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 07 2000 - 00:00:00 CDT