plsql - PL/SQL Trigger throws a MUTATING TABLE error -

i have trigger:

create or replace trigger tr14_2  before insert or update of cantidad on distribucion  each row declare   total_cars number;   total_cars_potential number; begin   select sum(cantidad) total_cars     distribucion     cifc = :new.cifc;   total_cars_potential := total_cars + :new.cantidad;    if inserting     if(total_cars_potential > 40)       raise_application_error(-20005, 'dealer [' || :new.cifc || '] has 40 cars stocked');     end if;   end if;    if updating     if(total_cars_potential - :old.cantidad > 40)       raise_application_error(-20006, 'that update of cantidad makes dealer exceeds limit of 40 cars stocked');     end if;   end if; end; 

it gets mutating table error, , have checked because of updating block of code, inserting goes ok; why? , how can fix it?

just clarify, want each dealer can have @ maximum 40 cars stocked. so, if add row distribucion ("distribution") cantidad ("quantity") make dealer exceed maximum stock, raise error. but, if update quantity of cars of type, stocked in database, , exceed 40 cars, want exception thrown.

thing is, not seeing mutatig table error on updating block.

1st: reason mutating table syndrome you're reading table updated in trigger (selecting total cars).

2nd: solution: i'd create 2 triggers: for-each-row trigger did collects updated rows in package variable

create or replace package pck_tr14_2   type changed_row record (         cantidad                distribucion.cantidad%type,   );   type changed_row_table table of changed_rows index binary_integer;   changed_rows changed_row_table;   cnt_changed_rows   binary_integer   default   1; end pck_tr14_2; / 

the for-each-row trigger (now after insert!)

create or replace trigger tr14_2  after insert or update of cantidad on distribucion  each row declare begin   pck_tr14_2.changed_rows(pck_tr14_2.cnt_changed_rows).cantidad := :old.cantidad;   pck_tr14_2.cnt_changed_rows := pck_tr14_2.cnt_changed_rows + 1; end; / 

then in after statement trigger implement logic:

create or replace trigger tr14_2_s after insert or update of cantidad on distribucion  begin   in pck_tr14_2.changed_rows.first..pck_tr14_2.changed_rows.last loop     -- logic here     null;   end loop; end; / 

access candidat in "virtuell" table needed ( pck_tr14_2.changed_rows(i). cantidad)