java.sql.BatchUpdateException: ORA-04091 on BEFORE INSERT TRIGGER

I'm getting a curious error on a BEFORE INSERT TRIGGER, which I can't understand. Even after reading multiple questions posted here with similar problems.

failed to process "method": category_id = 'foo' and request_id = '99' error: java.sql.BatchUpdateException: ORA-04091: table SCHEMA.ANIMAL_TABLE is mutating, trigger/function may not see it ORA-06512: at "SCHEMA.TRIGGER_NAME", line 7 ORA-04088: error during execution of trigger 'SCHEMA.TRIGGER_NAME'

Here is the trigger:

CREATE OR REPLACE TRIGGER TRIGGER_NAME 
BEFORE INSERT ON animal_table FOR EACH ROW WHEN (NEW.animal_type = 'cats')

DECLARE    base_animal_id NUMBER(19,0);   base_amount NUMBER(19,0);

BEGIN

SELECT animal_nbr INTO base_animal_id 
FROM animal_table 
WHERE category_id = :NEW.category_id AND summary_id = :NEW.summary_id 
AND animal_type = 'special'; 

SELECT animal_amount INTO base_amount 
FROM animal_table 
WHERE category_id = :NEW.category_id AND summary_id = :NEW.summary_id 
AND animal_type = 'special';


IF :NEW.category_id = 'foo' THEN

  :NEW.animal_info1 := base_animal_id;   
  :NEW.animal_info2 := base_amount;   
  :NEW.animal_info3 := '00';

END IF;

END;

I know the rules regarding modifications on the same table which the trigger is being held, but I also red something that it should work when changing new columns and only for the :NEW fields. I also thought it may be missing the UPDATE as trigger event, but that was not the case. Can anyone help me please? As I am new to triggers and PL/SQL.

Solution

The error message has nothing to do with updating the table. You cannot SELECT from the table that is currently being changed in a ROW level trigger.

The only workaround for this is to write the new rows into a intermediate table in the row level trigger. Then create a statement level trigger that processes all rows that have been written into the intermediate table (most probably only a single UPDATE statement with a subselect).

You might get away without the row level trigger and the intermediate table if you can identify the rows to be post-processed inside the statement level trigger e.g. by checking animal_type = 'cats' and category_id = 'foo'.

If that is the case, the following trigger (untested!!!) might do what you want (instead of the one you have)

CREATE OR REPLACE TRIGGER TRIGGER_NAME 
AFTER INSERT ON animal_table 
BEGIN

  UPDATE animal_table
     SET (animal_info1, 
          animal_info2, 
          animal_info3) = (SELECT animal_nbr, animal_amount, '00'
                           FROM animal_table t2
                           WHERE t2.category_id = animal_table.category_id
                             AND t2.sumary_id = animal_table.summary_id
                             AND t2.animal_type = 'special'
                          )
  WHERE animal_type = 'cats'
    AND category_id = 'foo'

END;

Another more general PL/SQL thing: You don't need to run one SELECT for each column you want to retrieve, you can do that in a single select statement if the conditions are the same:

SELECT animal_nbr, animal_amount
    INTO base_animal_id, base_amount 
FROM animal_table 
WHERE category_id = :NEW.category_id 
  AND summary_id = :NEW.summary_id 
  AND animal_type = 'special';

(Note the two columns in the select and into list)