Menu

Monthly Archives: February 2014

This article was written in 2011 and remains one of our most popular posts. If you’re keen to learn more about MySQL, you may find this recent article on administering MySQL of great interest.

This is the second article in a series about database automation with triggers and events. A trigger is SQL code which is run just before or just after an INSERT, UPDATE or DELETE event occurs on a particular database table. Triggers have been supported in MySQL since version 5.0.2.

Our Database Plan

We’ll create a small example database for a blogging application. Two tables are required:

`blog`: stores a unique post ID, the title, content, and a deleted flag.

`audit`: stores a basic set of historical changes with a record ID, the blog post ID, the change type (NEW, EDIT or DELETE) and the date/time of that change.

The following SQL creates the `audit` table. All columns are indexed and a foreign key is defined for audit.blog_id which references blog.id. Therefore, when we physically DELETE a blog entry, it’s full audit history is also removed.

Creating a Trigger

When a record is INSERTed into the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘NEW’ (or ‘DELETE’ if it was deleted immediately).

When a record is UPDATEd in the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘EDIT’ or ‘DELETE’ if the deleted flag is set.

Note that the changetime field will automatically be set to the current time.

Each trigger requires:

A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.

The table which triggers the event. A single trigger can only monitor a single table.

When the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A BEFORE trigger must be used if you need to modify incoming data. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table.

The trigger body; a set of SQL commands to run. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value). The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers.

As well as changing the post, a new entry appears in the `audit` table:

id

blog_id

changetype

changetime

1

1

NEW

2011-05-20 09:00:00

2

1

EDIT

2011-05-20 09:01:00

Finally, let’s mark the post as deleted:

UPDATE blog SET deleted = 1 WHERE id = 1;

The `audit` table is updated accordingly and we have a record of when changes occurred:

id

blog_id

changetype

changetime

1

1

NEW

2011-05-20 09:00:00

2

1

EDIT

2011-05-20 09:01:00

3

1

DELETE

2011-05-20 09:03:00

This is a simple example but I hope it’s provided some insight into the power of MySQL triggers. In my next post we’ll implement a scheduled event to archive deleted posts.

If you enjoyed reading this post, you’ll love Learnable; the place to learn fresh skills and techniques from the masters. Members get instant access to all of SitePoint’s ebooks and interactive online courses, like PHP & MySQL Web Development for Beginners.

By default MySQL treats semicolon(;) as the statement terminator or end of statement. But as we are going to use it inside the procedure body, so we need another different delimiter to state the end of the stored procedure. DELIMITER $$ sets $$ as the statement terminator.
The DROP PROCEDURE IF EXISTS statement checks for a duplicate stored procedure with the same name and if there exists any then issue a DROP command. You can skip this line if you are sure that there is no other stored procedure exists with the same name in your selected database.
Statement CREATE PROCEDURE marks the start of the stored procedure definition. Here, MyFirstSP is the name of our stored procedure. The stored procedure name followed by a pair of parentheses. The use of these parentheses is to define parameters inside it. In this stored procedure we don’t need any parameters, but we have to put these parentheses as this is mandatory in MySQL unlike SQL Server.
The BEGIN Statement marks the start or begining of a block (here the block is the stored procedure itself).In stored procedures, every statements with multiple statements should be enclosed with a block defined by BEGIN and END, where END statement marks the end of the block;
The statement inside the BEGIN .. END is a simple SELECT query, which fetches all the records from MyTable table.