Learning SQL - Triggers as Constraints

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.

Introduction

We saw in our previous lesson - Constraints, that Check Constraints could be very useful in enforcing the quality of data entered into our database. However they don't provide a lot of information when the Constraints are violated. A 'constraint failed' message is a bit feeble!

Triggers As Constraints

We've already seen in our Basic Triggers lesson that we can provide useful information messages so let's try implementing some triggers as constraints and see what happens. First we need to drop and recreate our Sports Teams table:

drop table QiSQL_Teams;

create table QiSQL_Teams (
id integer primary key,
sport text ,
team_name text,
nickname text,
founded_year,
ground_name text,
unique ( sport , team_name ) ) ;

We'll leave the Unique constraint on Sport, Team as the unique index constraint actually gives a reasonable error message - 'columns sport, team are not unique'.

Now lets try and recreate the Check Constraints we created in the previous lesson using triggers.

create trigger QiSQL_Teams_Insert_Trigger
before insert on QiSQL_Teams
for each row begin
select raise ( ABORT , 'Founding Year must be between 1800 and 2009' )
where new.founded_year not between 1800 and 2009;
end;

What happens when we try and insert some invalid data:

insert into QiSQL_Teams ( sport , team_name, nickname , founded_year, ground_name ) values (
'Football' , 'Tottenham Hotspur', 'Mighty Spurs!' , 1700 , 'White Hart Lane' );

Now we're talking! The error message is 'Founding Year must be between 1800 and 2009' just as we told the trigger it should be! Now we could add another Before Insert Trigger to handle the length of the team name column but how about if we were to add it to our QiSQL_Teams_trigger:

drop trigger QiSQL_Teams_Insert_Trigger;

create trigger QiSQL_Teams_Insert_Trigger
before insert on QiSQL_Teams
for each row begin
select raise ( ABORT , 'Founding Year must be between 1800 and 2009' )
where new.founded_year not between 1800 and 2009;
select raise ( ABORT , 'Team Name length Must be > 4 characters' )
where length(new.team_name) <=4;
end;

The message for the first found violation will be displayed and the Insert will be Aborted.

We need to do the same for a Before Update trigger to make sure updated values are also checked:

create trigger QiSQL_Teams_Update_Trigger
before update on QiSQL_Teams
for each row begin
select raise ( ABORT , 'Founding Year must be between 1800 and 2009' )
where new.founded_year not between 1800 and 2009;
select raise ( ABORT , 'Team Name length Must be > 4 characters' )
where length(new.team_name) <=4;
end;

We can now use the ever useful sqlite_master table to query the details of our triggers:

select sql from sqlite_master where tbl_name like 'QiSQL_Teams%' and type = 'trigger'

I don't know why you have to use a 'Like ...%' with the tbl_name in sqlite_master - maybe when I've got some time I'll check it out.