MySQL Stored Procedures and Triggers

How to Automate a MySQL Database with Triggers and Stored Procedures

Sep 30, 2008

MySQL Stored Procudures and Triggers, Mark Alexander Bain

MySQL stored procedures and triggers are a powerful tool in the hands of a database developer. This articles looks at how to use them to automate tasks on a database.

When MySQL released version 5.0.2 it introduced support for some very useful tools, in particular:

stored routines (otherwise known as stored procedures and stored functions)
triggers

When used in conjuction with each other MySQL stored procedures and triggers will provide a database that all but runs itself.

What is a MySQL Stored Procedure?

A MySQL stored procedure:

is a block of code stored on the server
will normally carry out a series of SQL statements

This is particularly useful because:

client applications need to know nothing about the structure or the content of a database - they just need to know how to run any MySQL stored procedures
any changes in procedures can be made via the stored procedures - those changes will automatically be used by client applications without the need to modify the applications

Of course, a stored procedure can only be run by some one or something and that's where the MySQL trigger is used.

What is a MySQL Trigger?

A MySQL trigger is a piece of code that fires whenever something happens to a table, and that something can be one of three table events:

delete - the trigger fires if something is deleted from the table
insert - the trigger fires if something is inserted into the table
update - the trigger fires if the table is updated

There is a further refinement as well - the trigger may be fired:

before the event occurs
after the event occurs

An Example MySQL Database with a Stored Procedure and Trigger

The SQL to create the database, tables, stored procedure and trigger can either be entered line by line or by creating a batch file and running that - either way the first step is to create a database and suitable tables:

drop database if exists staff_db;

create database staff_db;

use staff_db;

create table user (id int auto_increment, firstname varchar(25), surname varchar(25), username varchar(25), primary key (id));

create table task (id int auto_increment, details varchar(255), start datetime, end datetime, primary key (id));

create table task_user (id int auto_increment, task_id int, user_id int, primary key (id));

Next a stored procedure is needed - in this example the stored procedure will:

obtain the id of the last task created
identify the user with the least number of tasks
update the task_user table with the data gathered

delimiter //

create procedure set_task ()

begin

declare u_id int;

select user.id into u_id from user left join task_user

on user.id = task_user.user_id

group by user.id

order by count(task_user.user_id) asc

limit 1;

insert into task_user (task_id, user_id) values (last_insert_id(), u_id);

end

delimiter ;

And finally the trigger will need to be added - this will fire whenever a new row is inserted into the task table, and it will call the stored procedure that's just been created:

create trigger task_allocate after insert on task

for each row call set_task();

With all of the programming done then it's then just a matter of using the database and seeing what the effects are:

insert into user (username) values ('brown2');

insert into user (username) values ('jones1');

insert into user (username) values ('smith30');

insert into task (details, start) values ('Create tables', now());

insert into task (details, start) values ('Create a stored procedure', now());

insert into task (details, start) values ('Create a trigger', now());

insert into task (details, start) values ('Run some sql', now());

select start, username, details from task, task_user, user

where task_user.task_id = task.id

and task_user.user_id = user.id;

If all of the above SQL is saved into a text file entitled create_trigger.sql then the mysql command can be used to run it:

$ mysql -ubainm -pnot_saying create_trigger.sql

start username details

2008-09-30 17:59:30 brown2 Create tables

2008-09-30 17:59:30 jones1 Create a stored procedure

2008-09-30 17:59:30 smith30 Create a trigger

2008-09-30 17:59:30 brown2 Run some sql

Conclusion

MySQL triggers are an incredibly simple, yet effective, way of managing data in a database - the database user only needs to be aware of the data that they must enter, and if stored procedures are used then the programming will take care of everything else.