| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> help on trigger..
hi..
i have schools, teams and games table..
game references two teams and team references school..
normally i would have to populate school first , next team and game..
however when i insert a game with sufficient informations.. ( i used view
for additional data which will be used to populate school or team)
i want my trigger to populate all tables as neccessary(cascading insert?) thank you for help in advance..
here's my tables
create table schools (
scid integer primary key,
scname varchar(100) unique not null,
active char
create table teams(
teamid integer primary key,
scid integer references schools,
sport varchar(100),
gender char
create table games (
gameid integer primary key,
hometeamid integer references teams,
homepoints integer,
visitorteamid integer references teams,
visitorpoints integer,
gamedate date,
gamecity varchar(100),
finished char default 'n'
tsc_1 varchar(100),
tsc_2 varchar(100),
tsport varchar(100),
tgender char(1)
create or replace view games_view as
select g.gameid, g.hometeamid, g.visitorteamid, g.gamedate, g.gamecity,
tmp.tsc_1, tmp.tsc_2, tmp.tsport, tmp.tgender from games g, tmp_table tmp;
:vsc_1 := 'oklahoma';
:vsc_2 := 'texas';
:vsport := 'football';
:vgender := 'm';
:vdate := '05-oct-02';
:vcity := 'austin, tx';
:vgender := 'm';
end;
/
insert into games_view (
hometeamid,
visitorteamid,
gamedate,
gamecity,
tsc_1,
tsc_2,
tsport,
tgender
) values (
(select teamid
from teams t, schools s
where t.scid = s.scid and
s.name = :vsc_1 and
t.sport = :vsport and
t.gender = :vgender
),
(select teamid
from teams t, schools s
where t.scid = s.scid and
s.name = :vsc_2 and
t.sport = :vsport and
t.gender = :vgender
),
:vdate,
:vcity,
:vsc_1,
:vsc_2,
:vsport,
:vgender
val integer;
home_school_id integer; -- id which existed in db
visitor_school_id integer;
home_school_seq_id integer; --newly created schoolid
visitor_school_seq_id integer;
home_team_seq_id integer; -- newly created teamid
visitor_team_seq_id integer;
begin
--see if the game is already in db
select g.gameid into val
from games g
where (g.hometeamid = :new.visitorteamid or
g.visitorteamid = :new.hometeamid) and
g.gamedate = :new.gamedate;
--don't update or insert --using primary key constraint
if val is not null then
:new.gameid := val;
end if;
--teamid doesn't exist for supplied data
--let's make school & team as neccessary and create game at the end
exception
when no_data_found
then
-- let's check the homeschool exists
select count(*) into val
from schools s --?for loop
where s.scname = :new.tsc_1;
if (val = 1) then
--homeschool exists
-- dbms_output.put_line('homeschool exists');
select scid into home_school_id from schools s whe
re s.scname = :new.tsc_1; --eliminate
select count(*) into val from teams t where t.scid = home_school_id and
t.sport = :new.tsport and t.gender = :new.tgender;
if(val =1 ) then
--hometeam exists
-- dbms_output.put_line('hometeam exist');
null;
else
--hometeam doesn't exist
-- dbms_output.put_line('hometeam doesnt exist');-- insert team
select id_seq.nextval into home_team_seq_id from dual;
insert into teams ( teamid, scid, sport, gender ) values
( home_team_seq_id, home_school_id , :new.tsport, :new.tgender);
end if;
else
--homeschool doesn't exist
-- dbms_output.put_line('insert homeschool');
select id_seq.nextval into home_school_seq_id from dual;
select id_seq.nextval into home_team_seq_id from dual;
insert into schools (scid, scname) values (home_s
chool_seq_id, :new.tsc_1);
insert into teams ( teamid, scid, sport, gender ) values
( home_team_seq_id, school_id , :new.tsport, :new.tgender);
end if;
--ok let's examine visitor school (same as homeschool procedure)
select scid into val
from schools s
where s.scname = :new.tsc_2;
if (val = 1) then
-- dbms_output.put_line('visitorschool exists');
select scid into visitor_school_id from schools s whe
re s.scname = :new.tsc_2;
select count(*) into val from teams t where t.scid = visitor_school_id and
t.sport = :new.tsport and t.gender = :new.tgender;
if(val =1 ) then
-- dbms_output.put_line('visitorteam exist');
null;
else
-- dbms_output.put_line('visitorteam doesnt exist');-- insert team
select id_seq.nextval into visitor_team_seq_id from dual;
insert into teams ( teamid, scid, sport, gender ) values
( visitor_team_seq_id, visitor_school_id , :new.tsport, :new.tgender);
end if;
else
-- dbms_output.put_line('insert visitorschool');
select id_seq.nextval into visitor_school_seq_id from dual;
select id_seq.nextval into visitor_team_seq_id from dual;
insert into schools (scid, scname) values (visitor_s
chool_seq_id, :new.tsc_2);
insert into teams ( teamid, scid, sport, gender ) values
( visitor_team_seq_id, school_id , :new.tsport, :new.tgender);
end if;
-- home&visitor schools and teams are created as neccessary
-- let's create a game
insert into games ( hometeamid, visitorteamid, gamedate, gamecity )
values ( home_team_seq_id , visitor_team_se
q_id, :new.gamedate, :new.gamecity);
end;
/
show errors
create or replace trigger set_scid before insert on schools
for each row
declare
val integer;
begin
if :new.scid is null then
select id_seq.nextval into val from dual;
:new.scid := val;
end if;
create or replace trigger set_teamid before insert on teams
for each row
declare
val integer;
begin
if :new.teamid is null then
select id_seq.nextval into val from dual;
:new.teamid := val;
end if;
create or replace trigger set_gameid before insert on games
for each row
declare
val integer;
begin
if :new.gameid is null then
select id_seq.nextval into val from dual;
:new.gameid := val;
end if;
 |
 |