Re: mutating table error help

In article <am1fru$vkd$1_at_newsreader.mailgate.org>, eugene says...
>
>ouch.. this one is really hard to solve..
>i've looked at many past examples in groups.google.com
>but it's just overwheleming..
>
>.............
>
>
>create or replace trigger game_after after insert or update of gamedate,
>gamecity on games
>for each row
>declare val integer;
>begin
>select count(*) into val from games g
>where (g.hometeamid = :new.visitorteamid or
> g.hometeamid = :new.hometeamid or
> g.visitorteamid = :new.hometeamid or
> g.visitorteamid = :new.visitorteamid) and
> g.gamedate = :new.gamedate and
> g.gamecity <> :new.gamecity;
> if val > 0 then
> raise_application_error(-20000, 'No team may play two games
>in two different cities on the same day');
> end if;
>end;
>/
>show errors
>

See
http://asktom.oracle.com/~tkyte/Mutate/index.html

HOWEVER, don't forget about MVRC (multi versioning and read consistency). We have this little thing called non-blocking reads. In a multi-user environment, you would have to basically use select for updates in order to serialize otherwise two people inserting conflicting info at the same time will NOT see eachothers updates.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp