ORA-04091 Mutating tables problem 1

[xrodge]

Hi All,

I have a problem in that I am getting error ORA-04091 in a trigger I am writing. I can see why I get it, but not how to get around it.

The basic idea is, I have a table which can have any amount of rows for a project. If a row is deleted, I want to check if there are any other rows in the table for that project. If there are not, then I want to delete all of the staff assigned to that project.

My code is as follows:

CREATE OR REPLACE TRIGGER
EPROJECT3.CBR_DEL_BACO
BEFORE DELETE
on EPROJECT3.baco_temp
REFERENCING NEW AS NEW old as old
FOR EACH ROW

DECLARE

v_count NUMBER(3);

BEGIN

v_count := 0;

SELECT count(projectid)
INTO v_count
FROM baco_temp
WHERE projectid = ld.projectid;

dbms_output.put_line('count = ' || v_count);

IF v_count = 1
THEN

DELETE FROM projectroles_temp
WHERE projectid = ld.projectid
AND groupid = 209;

DELETE FROM accesscontrol_temp
WHERE projectid = ld.projectid;

END IF;

END;

So, when I delete a row, I'm not able to count how many rows are left in the table, which I really need to do!

I hope to get as quick and useful an answer as last time.

Thanks in advance!

Kenneth

 

[ghilpert]

Hi I have had problems like this and found creating a package table and puting(row delete trigger) into it the key(s) your need( a package table will exist as long as the transaction exist) then in an after statement go through the package table and then do your deletes. here is a example ( this is to give some idea the code is not correct)

package spec:
CREATE OR REPLACE PACKAGE SCHEMA.REMOVE_STAFT_PKG IS
PROCEDURE CLEAR;
PROCEDURE SET_ITERATOR;
PROCEDURE PUT_PROJECTID(PROJECTID IN TYPE?);
PROCEDURE DELETE_STAFT;
FUNCTION MORE_IN_TABLE RETURN BOOLEAN;
END SCHEMA.REMOVE_STAFT_PKG;

package body:
CREATE OR REPLACE PACKAGE BODY SCHEMA.REMOVE_STAFT_PKG IS
(this is a table defintion)
TYPE PROJECTID_TAB_TYPE IS TABLE OF TYPE?
INDEX BY BINARY_INTEGER;
(this create the table)
PROJECTID_TABLE PROJECTID_TAB_TYPE;
(this is used to index the table)
IDX BINARY_INTEGER := 0;
(this is used to access the table)
ITERATOR BINARY_INTEGER := 0;

PROCEDURE CLEAR IS
BEGIN
IDX := 0;
END CLEAR;
PROCEDURE SET_ITERATOR IS
BEGIN
ITERATOR:= IDX;
END;

PROCEDURE PUT_PROJECTID(P_PROJECTID IN TYPE?) IS
BEGIN
IDX := IDX + 1;
PROJECTID_TABLE(IDX) := P_PROJECTID;
END PUT_PROJECTID;

PROCEDURE DELETE_STAFT IS
(this will go through the table and then perform the deletes)
V_COUNT NUMBER(3);
V_PROJECTID TYPE?
BEGIN
SET_ITERATOR;
WHILE MORE_IN_TABLE LOOP

V_PROJECTID := PROJECTID_TABLE(ITERATOR);
ITERATOR := ITERATOR - 1;

SELECT COUNT(PROJECTID)
INTO V_COUNT
FROM BACO_TEMP
WHERE PROJECTID = V_PROJECTID;

DBMS_OUTPUT.PUT_LINE('COUNT = ' || V_COUNT);

IF V_COUNT = 1 THEN

DELETE FROM PROJECTROLES_TEMP
WHERE PROJECTID = V_PROJECTID
AND GROUPID = 209;

DELETE FROM ACCESSCONTROL_TEMP
WHERE PROJECTID = V_PROJECTID;

END IF;
END LOOP;
END DELETE_STAFT;

FUNCTION MORE_IN_TABLE RETURN BOOLEAN IS
BEGIN
RETURN (ITERATOR > 0);
END MORE_IN_TABLE;

END SCHEMA.REMOVE_STAFT_PKG;
/
in the before statement trigger you have:
(this clears the table)

CREATE OR REPLACE TRIGGER
EPROJECT3.CBR_DEL_BACO
BEFORE DELETE ON CTSO.CRED_HOLDER
BEGIN
CLEAR;
END;

in the delete row trigger you have:
(this adds the project to the table)
CREATE OR REPLACE TRIGGER
EPROJECT3.CBR_DEL_BACO
BEFORE DELETE
ON EPROJECT3.BACO_TEMP
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
PUT_PROJECTIDOLD.PROJECTID);
END;

in the after statement triger you have this:
CREATE OR REPLACE TRIGGER
EPROJECT3.CBR_DEL_BACO
AFTER DELETE
ON EPROJECT3.BACO_TEMP
BEGIN
DELETE_STAFT;
END;

Hope this helps.
p.s. you will have to put the package name in the calls

 

[xrodge]

Talk about a lifesaver!

The code looked a bit daunting to start, but looks to be running well. Does exactly what I need it to do.
Thanks for getting back to me on this ghilbert, your help has been much appreciated!

Kenneth