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;