Mutating table - trigger error

Question

I have to implement the following trigger:

The total number of votes, per election year, of the elections after 1960, does not exceed 538

However, I get the mutating table error. I understand why do I get the error but I cannot see another solution (with triggers). I could create a temporary table but I would like to have only the trigger. Here is the code:

 CREATE OR REPLACE TRIGGER restrict_election_votes
 after INSERT OR UPDATE ON election
 for each row
 declare 
v_nbofvotes number;
v_eleyr election.election_year%type :=:NEW.election_year;
v_votes election.votes%type :=:NEW.VOTES;

begin 
select sum(votes)
into v_nbofvotes
from election
where election_year=v_eleyr;

if(v_votes+v_nbofvotes >538) 
THEN
    RAISE_APPLICATION_ERROR(-20500, 'Too many votes');
  END IF;

END;


update election
set votes=175
where candidate='MCCAIN J'
and election_year=2008;

Answer 1

Assuming that the issue is that you need to query the election table because the vote count total is determined from multiple rows, then if you remove the "for each row" and make it a statement level trigger (will have to change the query to check rule for sum(votes) for all elections since 1960 as you don't know what row was inserted/updated) then it will work.

create table mb_elct (year varchar2(4), cand varchar2(30), vt number)

create or replace trigger mb_elct_trg
after insert or update on mb_elct
declare 
   v_nbofvotes number;
begin
select count(*) 
into  v_nbofvotes
from (
  select year, sum(vt)
    from mb_elct
  where  year > '1960'
  group by year
  having sum(vt) >538
);

if(nvl(v_nbofvotes,0) != 0 ) 
THEN
    RAISE_APPLICATION_ERROR(-20500, 'Too many votes');
  END IF;

END;
/

insert into mb_elct values ('2008', 'McCain', 500);

1 row inserted

update mb_elct set vt = vt + 200 where year = '2008' and cand = 'McCain';
ORA-20500: Too many votes
ORA-06512: at "EDR_ADMIN.MB_ELCT_TRG", line 16
ORA-04088: error during execution of trigger 'EDR_ADMIN.MB_ELCT_TRG'