Oracle SQL, avoiding errror "table is mutating" (trigger) -

i need trigger check if updated worker can moved other team

create table workers  (        id_worker number(4,0), --fk     id_team number(2,0) --fk ); 

my trigger looks like:

create or replace trigger team_limit before insert or update of id_team on workers each row declare   v_num number; begin   select count(*) v_num worker id_team=:new.id_team;   if v_num >= 5     raise_application_error(-20025,' error nr ... bleble');   end if; end;  

this generate error : "table %s.%s mutating, trigger/function may not see it" when row updated. how write statements not generate kind of error?

you can use compound trigger, looks (not tested):

create or replace trigger team_limit insert or update of id_team on workers  compound trigger  v_num number;  type row_tabletype table of workers.id_team%type; affectedteams row_tabletype;      before statement begin     affectedteams := row_tabletype(); -- init table variable    end before statement; ------------------- before each row begin     affectedteams.extend;     affectedteams(affectedteams.last) := :new.id_team; end before each row; -------------------- after statement begin     in affectedteams.first..affectedteams.last loop        select count(*) v_num worker id_team=affectedteams(i);        if v_num >= 5           raise_application_error(-20025,' error nr ... bleble');        end if;             end loop; end after statement;  end team_limit; /