Learning SQL - Triggers

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

A bit of Maintenance

First let's add a few more Children into the table we created in Joins Lesson. Execute these statements one at a time to add 3 more children for Susan Brown

insert into QiSQL_Children ( parent_id , first_name, last_name )
values ( 4 , 'Lucy' , 'Brown' );

insert into QiSQL_Children ( parent_id , first_name, last_name )
values ( 4 , 'Harry' , 'Brown' );

insert into QiSQL_Children ( parent_id , first_name, last_name )
values ( 4 , 'Quentin' , 'Brown' );

Pull the Trigger

A TRIGGER fires when a certain event occurs in the database. The events are the DML (Data Manipulation Language) events we learned about in the Basics Lesson - INSERT, UPDATE or DELETE.

Some versions of SQL offer a choice of firing triggers at Statement level or Row Level. Say for instance we were to issue these statements (Susan Brown and her children decided to change their last name to Biggins):

update QiSQL_Parents set last_name = 'Biggins' where id = 4;

update QiSQL_Children set last_name = 'Biggins' where parent_id = 4;

As you will obviously have noticed as you are a very clever reader - the first INSERT statement would have updated one row as their is only one record with the ID of 4, however the second UPDATE statement would have updated 4 rows - all the children of Susan Brown (now Biggins!). Lets' do a quick select to make sure:

select p.first_name 'Parent First Name' ,
p.last_name 'Parent Last Name',
c.first_name 'Child First Name' ,
c.last_name 'Child Last Name'
from QiSQL_Parents p ,
QiSQL_Children c
where p.id = c.parent_id ;

Yep this is what we expected to see:

Parent First Name Parent Last Name Child First Name Child Last Name
Susan Biggins Peggy Biggins
Susan Biggins Lucy Biggins
Susan Biggins Harry Biggins
Susan Biggins Quentin Biggins

Now, SQLite ONLY fires triggers at ROW LEVEL. So if we had applied a trigger that fired when we update the Parents table, it would have fired just once when we updated Susan's record, but would have fired four times on the Children table - one for each row (child) that we updated. Got it? Good. Now let's create a Trigger.

Create a New Trigger

Let's set up a table where we can store when changes have been made to our Children table:

create table QiSQL_Changes_History (
id integer primary key ,
table_name text ,
record_id numeric ,
change_date text ,
change_type text );

Notice that we have a column called table_name - now we can use this table to record changes in any of our tables

Now we can add a trigger to our database to insert a new record into this table whenever a record in the Children table is changed. We need a name for our trigger - let's call it QiSQL_change_trigger

create trigger QiSQL_change_trigger
after update on QiSQL_Children
begin
insert into QiSQL_Changes_History ( table_name , record_id , change_date , change_type ) values ('QiSQL_Children' , new.id , datetime ('now' ) , 'Update' );
end;

There are a couple of new things to point out here. In the insert part of the trigger we have used a value new.id. SQLite triggers use NEW and OLD references to the table being modified. If, for instance we create a trigger to be used when a particular column is updates we have the OLD value (before the update) and the NEW value (after the update). In our example we could have used old.id to get the value of the ID column in the table the trigger is firing on as IDs should never be changed!

The second part is datetime ('now') - this will be returned as today's date and time. There will be whole section on formatting and dates later.

To test our trigger let's suppose that Lucy Brown decides that she would like to called Lucille

update QiSQL_Children set first_name = 'Lucille'
where first_name = 'Lucy' and last_name = 'Biggins';

Now execute a query on the new QiSQL_Change to see if it has worked:

select
table_name' Table Name' ,
record_id' Record Id' ,
change_date ' Change Date',
change_type 'Change Type'
from qisql_changes_history;

This gives the following results (Change Date should be the date you executed the update statement):

Table Name Record Id Change Date
QiSQL_Children 2 2008-12-20 12:17:09

Now as a final test let's do a multiple row update on some Children - The Biggins family have decided to revert to the old family name Brown:

update QiSQL_Parents set last_name = 'Brown' where id = 4;

update QiSQL_Children set last_name = 'Brown' where parent_id = 4 ;

Our select statement now returns:

Table Name Record Id Change Date
QiSQL_Children 2 2008-12-20 12:17:09
QiSQL_Children 1 2008-12-20 12:22:14
QiSQL_Children 2 2008-12-20 12:22:14
QiSQL_Children 3 2008-12-20 12:22:14
QiSQL_Children 4 2008-12-20 12:22:14

OK, great. In the Views Lesson section we will have a look at how we can make reports like this show more meaningful data as Record Id doesn't really tell us much.

Before we end this lesson let's just create one more tigger on our QiSQL_Children table to record when data is deleted:

create trigger QiSQL_children_delete_trigger
after delete on QiSQL_Children
begin
insert into QiSQL_Changes_History ( table_name , record_id , change_date ) values ('QiSQL_Children' , new.id , datetime ('now' ) , 'Deleted' );
end;