DBMS triggers

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;