SQL>
REM 1. If a tuple is inserted into Outcomes, check that the ship and
battle are listed in Ships and
SQL>
REM Battles, respectively, and if not, insert tuples into one or both
of these relations, with NULL
SQL>
REM components where necessary.
SQL>
SQL>
SQL>
create or replace trigger trig_check
2
before insert on outcomes
3
for each row
4
declare
5
sh outcomes.ship%type;
6
bat outcomes.battle%type;
7
cursor c1 is select s.name into sh from ships s where
s.name=:new.ship;
8
cursor c2 is select b.name into bat from battles b where
b.name=:new.battle;
9
begin
10
open c1;
11
fetch c1 into sh;
12
if c1%notfound then
13
insert into ships values(:new.ship,NULL,NULL);
14
end if;
15
close c1;
16
open c2;
17
fetch c2 into bat;
18
if c2%notfound then
19
insert into battles values(:new.battle,NULL);
20
end if;
21
close c2;
22
end;
23
/
Trigger
created.
SQL>
SQL>
insert into outcomes values('vishak','Guadalcanal','ok');
1
row created.
SQL>
insert into outcomes values('Hiei','second War','ok');
1
row created.
SQL>
SQL>
select * from ships where name='vishak';
NAME
CLASS LAUNCHED
---------------
--------------- ----------
vishak
SQL>
select * from battles where name='second War';
NAME
BATTLE_DA
---------------
---------
second
War
SQL>
SQL>
insert into outcomes values('wing','new war','ok');
1
row created.
SQL>
select * from ships where name='wing';
NAME
CLASS LAUNCHED
---------------
--------------- ----------
wing
SQL>
select * from battles where name='new war';
NAME
BATTLE_DA
---------------
---------
new
war
SQL>
SQL>
drop trigger trig_check;
Trigger
dropped.
SQL>
REM 4.Check under all circumstances that could cause a violation,
that no ship fought in a battle
SQL>
REM that was at a latter date than another battle in which that ship
was sunk.
SQL>
SQL>
create or replace trigger sunk_check
2
before insert on outcomes
3
for each row
4
declare
5
cursor cs is select battle_date from battles join outcomes on
(battle=name)
6
where result='sunk' and ship=:new.ship;
7
bd battles.battle_date%type;
8
ent battles.battle_date%type;
9
begin
10
if inserting then
11
select battle_date into bd from battles where name=:new.battle;
12
open cs;
13
fetch cs into ent;
14
if (bd>ent) then
15
raise_application_error(-20003,'Ship already sunk');
16
end if;
17
end if;
18
end;
19
/
Trigger
created.
SQL>
SQL>
insert into outcomes values('North Carolina','Surigao Strait','ok');
insert
into outcomes values('North Carolina','Surigao Strait','ok')
*
ERROR
at line 1:
ORA-20003:
Ship already sunk
ORA-06512:
at "SYSTEM.SUNK_CHECK", line 12
ORA-04088:
error during execution of trigger 'SYSTEM.SUNK_CHECK'
SQL>
drop trigger sunk_check;
Trigger
dropped.
SQL>
SQL>
REM 5.When making any modification to the view – 2 that is created
in assignment – 4, ensure th
at
SQL>
REM the modification is reflected in the corresponding base table(s)
also.
SQL>
SQL>
alter table classes disable all triggers;
Table
altered.
SQL>
alter table ships disable all triggers;
Table
altered.
SQL>
alter table battles disable all triggers;
Table
altered.
SQL>
alter table outcomes disable all triggers;
Table
altered.
SQL>
SQL>
CREATE OR REPLACE VIEW Kongo_Yamato
2
AS SELECT s.class,o.ship,s.launched,o.battle,o.result
3
FROM ships s join outcomes o on(s.name=o.ship)
4
WHERE s.class in ('Kongo','Yamato');
View
created.
SQL>
SQL>
SQL>
CREATE or REPLACE trigger trig_kongo
2
INSTEAD OF
3
INSERT OR UPDATE OR DELETE ON Kongo_Yamato
4
for each row
5
begin
6
if inserting then
7
dbms_output.put_line(' Inserting.....');
8
insert into ships values(:new.ship,:new.class,:new.launched);
9
insert into outcomes values(:new.ship,:new.battle,:new.result);
10
elsif updating then
11
dbms_output.put_line(' Updating......');
12
if updating('class')
13
then
14
update ships
15
set class=:new.class
16
where name=:old.ship;
17
end if;
18
if updating('launched')
19
then
20
update ships
21
set launched=:new.launched
22
where name=:old.ship;
23
end if;
24
if updating('battle')
25
then
26
update outcomes
27
set battle=:new.battle
28
where ship=:old.ship;
29
end if;
30
if updating('result')
31
then
32
update outcomes
33
set result=:new.result
34
where ship=:old.ship;
35
end if;
36
elsif deleting then
37
dbms_output.put_line(' Deleting......');
38
delete from outcomes where ship=:old.ship;
39
delete from ships where name=:old.ship;
40
end if;
41
end;
42
/
Trigger
created.
SQL>
SQL>
REM INSERTION
SQL>
SQL>
insert into Kongo_Yamato values('Yamato','new
ships',1943,'Guadalcanal','ok');
1
row created.
SQL>
select * from ships where name='new ships';
NAME
CLASS LAUNCHED
---------------
--------------- ----------
new
ships Yamato 1943
SQL>
select * from outcomes where ship='new ships' and
battle='Guadalcanal';
SHIP
BATTLE RESULT
---------------
--------------- ----------
new
ships Guadalcanal ok
SQL>
SQL>
SQL>
REM UPDATION
SQL>
SQL>
update Kongo_Yamato
2
set class='Kongo'
3
where ship='new ships' and battle='Guadalcanal';
1
row updated.
SQL>
SQL>
update Kongo_Yamato
2
set launched=1918
3
where ship='new ships' and battle='Guadalcanal';
1
row updated.
SQL>
SQL>
update Kongo_Yamato
2
set battle='North Atlantic'
3
where ship='new ships' and battle='Guadalcanal';
1
row updated.
SQL>
SQL>
SQL>
update Kongo_Yamato
2
set result='damaged'
3
where ship='new ships' and battle='North Atlantic';
1
row updated.
SQL>
SQL>
select * from ships where name='new ships';
NAME
CLASS LAUNCHED
---------------
--------------- ----------
new
ships Kongo 1918
SQL>
select * from outcomes where ship='new ships' and battle='North
Atlantic';
SHIP
BATTLE RESULT
---------------
--------------- ----------
new
ships North Atlantic damaged
SQL>
SQL>
delete from Kongo_Yamato where ship='new ships';
1
row deleted.
SQL>
select * from ships where name='new ships';
no
rows selected
SQL>
select * from outcomes where ship='new ships';
no
rows selected
SQL>
SQL>
drop trigger trig_kongo;
Trigger
dropped.
SQL>
SPOOL OFF;
No comments:
Post a Comment