CODEWITHSUNDEEP
oracle-database
Jonathan Chan
Jonathan Chan

Reputation: 563

Mutating Error in Oracle trigger

Help, I am very new to oracle trigger. What I trying to do is when delete C last row of B, A shall not have B. So I create a trigger on C.

CREATE OR REPLACE TRIGGER child_check    
 BEFORE DELETE DELETE ON C

   REFERENCING OLD as OLD   
FOR EACH ROW DECLARE    
rowcnt number;  
rowcnt1 number;

BEGIN
    SELECT COUNT(*) INTO rowcnt FROM C WHERE colB = :OLD.colB;
    IF rowcnt > 1 THEN
     DBMS_OUTPUT.PUT_LINE('DELETE');
        DELETE C WHERE ROWID = :OLD.ROWID;
    ELSE
        SELECT COUNT(*) INTO rowcnt1 FROM A WHERE colB = :OLD.colB;
        IF rowcnt1 > 1 THEN
         DBMS_OUTPUT.PUT_LINE('B IS USED IN A. C CANNOT REMOVE ALL OF B');
            ELSE
             DBMS_OUTPUT.PUT_LINE('DELETE');
            DELETE C WHERE ROWID = :OLD.ROWID;
            DELETE B WHERE colB = :OLD.colB;
        END IF;      
        END IF;

END; /

   TABLE A    | TABLE B |   TABLE C
colA  | colB  |  colB   | colC | colB

A.colA is PK B.colB is PK C.colC is PK

A.colB FK of B.colB C.colB FK of B.colB

There is a mutating error. I do a google search and I understand there is something got to do with the delete. I cannot find a concrete solution Can someone enlighten me how and what to change in the delete query?

Upvotes: 0

Views: 121

Answers (2)

sql_dummy
sql_dummy

Reputation: 739

Also apart from Mutating Trigger error this could also cause the triggers to execute recursively and fail, as your Delete (on c) statement in the trigger would start an other trigger and this happens repeatedly.

Upvotes: 0

Rene
Rene

Reputation: 10551

In Oracle you cannot perform select, insert, update or delete on the table the row level trigger is on. In your case the trigger is on C so you are not allowed to delete records from C.

Your trigger seems a bit strange. The trigger fires when a record is deleted from C. And then the trigger is also deleting that record from C. That is not necessary. The record will be deleted.

Deleting from B is possible.

Your code is hard to understand. B is a column in C but also the name of a table? Very confusing.

Upvotes: 1

Related Questions