Megan Taylor

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

Intro to Databases: Constraints and Triggers, Triggers Demo (1 & 2)

Features covered:
Before and After; Insert, Delete, and Update
New and Old
Conditions and actions
Triggers enforcing constraints
Trigger chaining
Self-triggering, cycles
Conflicts
Nested trigger invocations

No DBMS implements exact standard, some deviate considerably in both syntax and behavior.
Postgres
expressiveness/behavior = full standard row-level + statement-level, old/new row and table
cumbersome and awkward syntax
SQLite
row-level only, immediate activation, no old/new table
MySQL
row-level only, immediate activation, no old/new table
only one trigger per event type
limited trigger chaining

Demo will use SQLite
For Each Row is implicit if not specified
No Old Table or New Table
No Referencing clause
– Old and New predefined for Old Row and New Row
Tigger action: SQL statement in begin-end block

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

create trigger R1
after insert on Student
for each row
when New.GPA > 3.3 and New.GPA <= 3.6 begin insert into Apply values (New.sID, 'Stanford', 'geology', null); insert into Apply values (New.sID, 'MIT', 'biology', null); end; create trigger R2 after delete on Student for each row begin delete from Apply when sID = Old.sID; end; create trigger R3 after update of cName on College for each row begin update Apply set cName = New.cName where cName = Old.cName; end; create trigger R4 before insert on College for each row when exists (select * from College where cName = New.cName) begin select raise(ignore); end; create trigger R5 before update of cName on College for each row when exists (select * from College where cName = New.cName) begin select raise(ignore); end; create trigger R6 after insert on Apply for each row when (select count(*) from Apply where cName = New.cName) > 10
begin
update College set cName = cName || ‘-Done’
where cName = New.cName;
end;

create trigger R7
before insert on Student
for each row
when New.sizeHS < 100 or New.sizeHS > 5000
begin
select raise(ignore)
end;

create trigger AutoAccept
after insert on Apply
for each row
when (New.cName = ‘Berkeley’ and
3.7 < (select GPA from Student where sID = New.sID) and 1200 < (select sizeHS from Student where sID = New.sID)) begin update Apply set decision = 'Y' where sID = New.sID and cName = New.cName; end; create trigger TooMany after update of enrollment on College for each row when (Old.enrollment <= 16000 and New.enrollment > 16000)
begin
delete from Apply
where cName = New.cName and major =’EE’;
update Apply
set decision = ‘U’
where cName = New.cName
and decision = ‘Y’;
end;

create trigger R8
after insert on T1
for each row
when (select count(*) from T1) < 10 - termination condition to fix recursive triggers begin insert into T1 values (New.A+1); end; create trigger R9 after insert on T1 for each row begin insert into T2 values (New.A+1); end; create trigger R10 after insert on T2 for each row begin insert into T3 values (New.A+1); end; create trigger R11 after insert on T3 for each row begin insert into T1 values (New.A+1); end; create trigger R12 after insert on T1 for each row begin update T2 set A = 2; end; create trigger R13 after insert on T1 for each row when exists (select * from T1 where A = 2) begin update T1 set A = 3; end; create trigger R14 after insert on T1 for each row begin insert into T2 values (1); insert into T3 values (1); end; create trigger R15 after insert on T2 for each row begin insert into T3 values (2); insert into T4 values (2); end; create trigger R16 after insert on T3 for each row begin insert into T4 values (3); end; create trigger R17 after insert on T1 for each row begin insert into T2 select avg(A) from T1; end;

November 28, 2011 | Comments Off on Intro to Databases: Constraints and Triggers, Triggers Demo (1 & 2) | Categories: Posts | Permalink

Comments are closed.

%d bloggers like this: