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 log('inserted',a.val)

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

create trigger logdelete before delete on test1 referencing old row as a for each row 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

In SQL2003 the body of a trigger must be a procedure call statement, as here. Triggers are particularly useful if the procedure call in question is to an external procedure, i.e. a procedure in the database client application.