Re: mutating table error help

From: eugene kim <eugene1977_at_hotmail.com>
Date: Mon, 16 Sep 2002 03:55:00 +0000
Message-ID: <am2rag$tcn$1@newsreader.mailgate.org>

k.. i managed to make it work, but can't understand why this one works and the one i tried didn't work....
basically, all i changed is
reading columns from package_table(?) which i populated in before trigger instead of
reading columns using :new

if i understand 'mutating table' correctly, it's a table which is in transaction.
then, how i could in my solution..
select count(*) from games(the table i'm working on) where some condition; if games table is mutating, then shouldn't oracle raise error when i select count(*) from games?

thank you

this is what i had

---

create or replace trigger game_after after insert or update of gamedate, gamecity on games
begin
:new.gamedate := upper(:new.gamedate);
:new.gamecity := upper(:new.gamecity);

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;

select id_seq.nextval into val from dual;

        :new.gameid := val;
        end if;

end;
/
show errors

---

this is what i have now

---

CREATE OR REPLACE PACKAGE pkg_game_table AS
TYPE game_tbl_type IS TABLE OF games%ROWTYPE INDEX BY BINARY_INTEGER;

gvar_game_tbl game_tbl_type;
gvar_count BINARY_INTEGER := 0;

END pkg_game_table;
/
show errors

create or replace trigger set_gameid before insert or update of gamedate, gamecity on games
for each row
declare val integer;

begin
:new.gamedate := upper(:new.gamedate);
:new.gamecity := upper(:new.gamecity);

pkg_game_table.gvar_count := pkg_game_table.gvar_count + 1; pkg_game_table.gvar_game_tbl(pkg_game_table.gvar_count).gameid :=
:new.gameid;

 pkg_game_table.gvar_game_tbl(pkg_game_table.gvar_count).gamedate :=
:new.gamedate;

 pkg_game_table.gvar_game_tbl(pkg_game_table.gvar_count).gamecity :=
:new.gamecity;

 pkg_game_table.gvar_game_tbl(pkg_game_table.gvar_count).hometeamid :=
:new.hometeamid;

 pkg_game_table.gvar_game_tbl(pkg_game_table.gvar_count).visitorteamid :=
:new.visitorteamid;

        if :new.gameid is null then
select id_seq.nextval into val from dual;

        :new.gameid := val;
        end if;

        end;

/
show errors

create or replace trigger game_after after insert or update of gamedate, gamecity on games
declare
val integer;
var_idx binary_integer;
begin
for var_idx in 1 .. pkg_game_table.gvar_count loop

select count(*) into val from games g
where (g.hometeamid = pkg_game_table.gvar_game_tbl(var_idx).visitorteamid  or

        g.hometeamid = pkg_game_table.gvar_game_tbl(var_idx).hometeamid or
        g.visitorteamid = pkg_game_table.gvar_game_tbl(var_idx).hometeamid 
or 
        g.visitorteamid = pkg_game_table.gvar_game_tbl(var_idx).visitorteamid) and
        g.gamedate = pkg_game_table.gvar_game_tbl(var_idx).gamedate and
        g.gamecity <> pkg_game_table.gvar_game_tbl(var_idx).gamecity;
        if val > 0 then
        pkg_game_table.gvar_count := 0;
        raise_application_error(-20000, 'No team may play two games 
in two different cities on the same day');
        end if;

end loop;
pkg_game_table.gvar_count := 0;
end;
/
show errors

---

Received on Sun Sep 15 2002 - 22:55:00 CDT