Hi!
Let's say I've got a this table:
create table testcase (
id number,
color varchar2(10),
winner char(1) default 'N');
create or replace trigger check_only_one_winner
after insert or update of winner on testcase
for each row
when (new.winner = 'Y')
Declare
l_winner_id testcase.id%type;
begin
select winner
into l_winner_id
from testcase
where color = :new.color;
if :new.id <> l_winner_id then
raise_application_error (-20000, 'Only one winner per color');
end if;
exception
When No_Data_Found then
Null;
end;
/
insert into testcase values (1, 'red', 'N');
-- OK
insert into testcase values (2, 'blue', 'Y');
-- Mutating table ERROR
How would you face this problem?