The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

Triggers

SQL2003 supports triggers.

Pyrrho has built-in facilities to do activity logging (see section 3.5 and 8.2). However, triggers allow for a more customizable approach as the following example shows:

create table test1(id int primary key,val char)

create table test2(id int primary key,ent char,val char)

create procedure log(g char,h char) insert into test2(ent,val) values(g,h)

create trigger loginsert after insert on test1 referencing new row as a for each row call log('inserted',a.val)

create trigger logupdate before update on test1 referencing old row as a new row as b for each row call log(a.val,b.val)

create trigger logdelete before delete on test1 referencing old row as a for each row call log('deleted',a.val)

insert into test1 values(1,'First'),(2,'Second')

table test2

update test1 set val='New One' where id=1

table test2

delete from test1 where id=2

table test2