ORA-04091. Table is Mutating

Question

i want to make the value of two columns in different tables mutually exclusive

Table 1 (ANIMAL):

CREATE TABLE  "ANIMAL" 
(   "ANIMALID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "NAME" VARCHAR2(255) NOT NULL ENABLE, 
    "TYPEFK" NUMBER NOT NULL ENABLE, 
    "BREEDFK" NUMBER, 
    "DATEOFBIRTH" DATE, 
    "SEX" CHAR(1) NOT NULL ENABLE, 
    "ADMITTANCEDATE" DATE NOT NULL ENABLE, 
    "AVAILABLETOFOSTER" CHAR(1) NOT NULL ENABLE, 
     CONSTRAINT "ANIMAL_PK" PRIMARY KEY ("ANIMALID")
  USING INDEX  ENABLE
   )
/ 

Table 2 (FOSTERAPPLICATION):

CREATE TABLE  "FOSTERAPPLICATION" 
   (    "APPLICATIONID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "APPLICANTFK" NUMBER NOT NULL ENABLE, 
    "ANIMALFK" NUMBER NOT NULL ENABLE, 
    "DATEOFAPPLICATION" DATE NOT NULL ENABLE, 
    "REASONOFAPPLICATION" BLOB NOT NULL ENABLE, 
    "APPLICATIONAPPROVED" CHAR(1), 
     CONSTRAINT "FOSTERAPPLICATION_PK" PRIMARY KEY ("APPLICATIONID")
  USING INDEX  ENABLE
   )
/

i have two triggers:

Trigger 1(test_trig): 'AVAILABLETOFOSTER' = 'N' when APPLICATIONAPPROVED = 'Y'

CREATE OR REPLACE TRIGGER test_trig
AFTER INSERT OR UPDATE ON  FOSTERAPPLICATION
FOR EACH ROW
BEGIN
UPDATE ANIMAL
SET AVAILABLETOFOSTER = 'N'
WHERE :NEW.APPLICATIONAPPROVED = 'Y'
AND :NEW.ANIMALFK = ANIMALID;
END test_trig;
/

Trigger 2(test_trig2): APPLICATIONAPPROVED = 'N' when AVAILABLETOFOSTER = 'Y'

CREATE OR REPLACE TRIGGER test_trig2
AFTER INSERT OR UPDATE ON ANIMAL
FOR EACH ROW
BEGIN
UPDATE FOSTERAPPLICATION
SET APPLICATIONAPPROVED = 'N'
WHERE :NEW.AVAILABLETOFOSTER = 'Y'
AND :NEW.ANIMALID = ANIMALFK;
END test_trig2;
/

the concept is: an application to foster an animal can only be approved (made 'Y') if the animal is available to be fostered in the first place and vice versa

i want the change in values to trigger automatically but i get a mutating error. i understand the error happens because i am using 'UPDATE' in my trigger ... any way around this?

Answer 1

What you have attempted to do here is called a "trigger loop". Let's consider how it might happen:

1. You update the ANIMAL table.
2. Trigger test_trig_2 on the ANIMAL table fires, and updates FOSTERAPPLICATION.
3. Trigger test_trig_1 on the FOSTERAPPLICATION table fires, and updates ANIMAL table.
4. Trigger test_trig_2 on the ANIMAL table fires, and updates FOSTERAPPLICATION.
5. Trigger test_trig_1 on the FOSTERAPPLICATION table fires, and updates ANIMAL table.
6. Trigger test_trig_2 on the ANIMAL table fires, and updates FOSTERAPPLICATION.
7. Trigger test_trig_1 on the FOSTERAPPLICATION table fires, and updates ANIMAL table.
8. Trigger test_trig_2 on the ANIMAL table fires, and updates FOSTERAPPLICATION.
9. Trigger test_trig_1 on the FOSTERAPPLICATION table fires, and updates ANIMAL table.
10. And so on, infinitely.

This is one of the reasons why Oracle does not allow the table on which a trigger has been defined to be updated by that trigger, directly OR INDIRECTLY. Here, for example, the test_trig_2 trigger on ANIMAL does not attempt to directly update the ANIMAL table - but by updating FOSTERAPPLICATION it indirectly tries to update ANIMAL when the test_trig_1 trigger fires.

This is why doing any sort of logic of this nature in triggers is a Bad Idea. Rather than doing this I suggest writing a procedure which performs all the updates you need to have performed, and then use that procedure whenever that functionality is needed.

Answer 2

The logic to decide whether the update needs to happen is in your update statement as a where clause, at this point it is too late. Instead, you can just use a good ol' fashioned if statement in your Trigger PL/SQL.

DB Fiddle using your demo DDL (after fixing it up)