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;
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'