Megan Taylor

front-end dev, volunteacher, news & data junkie, bibliophile, Flyers fan, sci-fi geek and kitteh servant

Intro to Databases: Views, View Modifications using Triggers

Unlike queries, cannot be automated in general

1. Rewriting process specified explicitly by view creator
– Using special INSTEAD-OF trigger

Demo: simple college admissions database
College(cName, state, enrollment)
Student(sID, sName, GPA, sizeHS)
Apply(sID, cName, major, decision)

Example 1

create view CSaccept as
select sID, cName
from Apply
where major = ‘CS’ and decision = ‘Y’;

delete from CSaccept where sID = ‘123’; – ERROR

Trigger
create trigger CSacceptDelete
instead of delete on CSaccept
for each row
begin
delete from Apply
where sID = Old.sID
and cName = Old.cName
and major = ‘CS’ and decision = ‘Y’;
end;

uupdate CSaccept set cName = ‘CMU’ where sID = 345;

Trigger
create trigger CSacceptUpdate
instead of update of cName on CSaccept
for each row
begin
update Apply
set cName = New.cName
where sID = Old.sID
and cName = Old.cName
and major = ‘CS’ and decision = ‘Y’;
end;

Example 2

create view CSEE as
select sID, cName, major
from Apply
where major = ‘CS’ or major = ‘EE’;

insert into CSEE values (111, ‘Berkeley’, ‘CS’)

Trigger
create trigger CSEEinsert
instead of insert on CSEE
for each row
begin
insert into Apply values (New.sID, New.cName, New.major, null);
end;

insert into CSEE values (222, ‘Berkeley’, ‘biology’)

Need to write better triggers to prevent users from write insert commands against a view that affect the base tables but don’t get reflected in the view because they don’t satisfy the conditions.

Better Trigger
create trigger CSEEinsert
instead of insert on CSEE
for each row
when New.major = ‘CS’ or New.major = ‘EE’
begin
insert into Apply values (New.sID, New.cName, New.major, null);
end;

December 2, 2011 | Comments Off on Intro to Databases: Views, View Modifications using Triggers | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: