ORA-04091: table ***** is mutating, trigger/function may not see it

Question

I have the following error:

ORA-04091: table SYSTEM.ORDINE is mutating, trigger/function may not see it

On this trigger PL/SQL:

create or replace trigger T_Ordine
after insert on Ordine
for each row

DECLARE
conta number := 0;
t_o exception;

BEGIN
select count(*) into conta
from Cliente join Ordine on Cliente.ID_Cliente = Ordine.ID_CLiente
where Cliente.C_CF_Rivenditore <> Ordine.CF_Rivenditore;

if conta > 0 then
raise t_o;
end if;

EXCEPTION
when t_o then
raise_application_error(-20002,'Un rivenditore non puo ricevere ordini da un cliente non suo');
end;
/

I think that the error caused from the modification of table Ordine in the join with table Cliente.

Answer 1

Since I am italian, I am a little advantaged in understanding what you are trying to do:

1. "Ordine" is the table of orders (like product orders).
2. "rivenditore" means "seller".
3. "cliente" means customer.
4. in the "customer" table there is a field (C_CF_Rivenditore) that imposes the seller that should be used for orders issued by the customer.
5. the "orders" table contains a reference to the customer and a reference to the seller receiving the order.

you simply want to make it impossible to insert an order for a seller different from the one designated for each customer (this is what your error message says), but you don't know how to use :new or :old, so you wrote the test that way (which isn't at all the best method of doing it, since you are re-checking all the orders in the table every time a new order is inserted).

This is what you really want to write:

      create or replace trigger T_Ordine
       after insert on Ordine
       for each row

      DECLARE
        rivenditore_del_cliente  Cliente.C_CF_Rivenditore%type;

      BEGIN
       select  Cliente.C_CF_Rivenditore
         into  rivenditore_del_cliente
         from Cliente
        where Cliente.ID_Cliente = :new.ID_CLiente

        if rivenditore_del_cliente <> :new.CF_Rivenditore  then
            raise raise_application_error(-20002,
                         'Un rivenditore non puo ricevere ordini da un cliente non suo');
        end if;  
      end;

the above trigger might need to be extended with some further checks if some of these are true:

1. id_cliente is not the primary key of "cliente"
2. ordine.id_cliente is not mandatory
3. there isn't a foreign key constraint that ensures that ordine.id_cliente is a valid ID_cliente of the clienti table.

Answer 2

Your trigger is a little odd.

You've declare it for each row, yet you never use :new to access any of the inserted values.

As far as I can see, there are two ways to fix your trigger:

1. Make the trigger a statement-level trigger, so that it runs once after the ordine table is inserted into, regardless of how many rows are inserted. To do this, simply delete the line for each row.

2. Adjust the trigger to only check the inserted order, rather than every order in the table. To do this, replace the SQL query you use to find conta with the following:

   select count(*) into conta
   from Cliente 
   where Cliente.ID_Cliente = :new.ID_CLiente
   and Cliente.C_CF_Rivenditore <> :new.CF_Rivenditore;
   

   Note that we are no longer querying the Ordine table - the details of the row that has just been inserted are available as :new.column_name. This gets around the ORA-04091 error.

I would recommend the second approach. The query you use to find conta currently searches the whole of the Ordine table, and as your application gains more and more orders, this trigger gets slower and slower as the query searches through more and more data. Also, you probably don't want your application to refuse to take any orders from anyone if it happens that there's one order somewhere in the system where the client's Rivenditore doesn't match the order's Rivenditore.

Incidentally, there's not a lot of point raising the exception t_o, catching it and raising an alternative exception. Just raise the second exception straight away, i.e.:

if conta > 0 then
raise_application_error(-20002,'Un rivenditore non puo ricevere ordini da un cliente non suo');
end if;