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 `blog` and indexes the deleted column:
CREATE TABLE `blog` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` text,
`content` text,
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `ix_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Blog posts';
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.
CREATE TABLE `audit` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`blog_id` mediumint(8) unsigned NOT NULL,
`changetype` enum('NEW','EDIT','DELETE') NOT NULL,
`changetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `ix_blog_id` (`blog_id`),
KEY `ix_changetype` (`changetype`),
KEY `ix_changetime` (`changetime`),
CONSTRAINT `FK_audit_blog_id` FOREIGN KEY (`blog_id`) REFERENCES `blog` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Creating a Trigger
We now require two triggers:
- 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.
The basic trigger syntax is:
CREATE
TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `database`.`table`
FOR EACH ROW BEGIN
-- trigger body
-- this code is applied to every
-- inserted/updated/deleted row
END;
We require two triggers — AFTER INSERT and AFTER UPDATE on the blog table. It’s not necessary to define a DELETE trigger since a post is marked as deleted by setting it’s deleted field to true.
The first MySQL command we’ll issue is a little unusual:
DELIMITER $$
Our trigger body requires a number of SQL commands separated by a semi-colon (;). To create the full trigger code we must change delimiter to something else — such as $$.
Our AFTER INSERT trigger can now be defined. It determines whether the deleted flag is set, sets the @changetype variable accordingly, and inserts a new record into the audit table:
CREATE
TRIGGER `blog_after_insert` AFTER INSERT
ON `blog`
FOR EACH ROW BEGIN
IF NEW.deleted THEN
SET @changetype = 'DELETE';
ELSE
SET @changetype = 'NEW';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
END$$
Finally, we set the delimiter back to a semi-colon:
DELIMITER ;
The AFTER UPDATE trigger is almost identical:
DELIMITER $$
CREATE
TRIGGER `blog_after_update` AFTER UPDATE
ON `blog`
FOR EACH ROW BEGIN
IF NEW.deleted THEN
SET @changetype = 'DELETE';
ELSE
SET @changetype = 'EDIT';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);
END$$
DELIMITER ;
It’s beyond the scope of this article, but you could consider calling a single stored procedure which handles both triggers.
Trigger Happy?
Let’s see what happens when we insert a new post into our blog table:
INSERT INTO blog (title, content) VALUES ('Article One', 'Initial text.');
A new entry appears in the `blog` table as you’d expect:
| id |
title |
content |
deleted |
| 1 |
Article One |
Initial text |
0 |
In addition, a new entry appears in our `audit` table:
| id |
blog_id |
changetype |
changetime |
| 1 |
1 |
NEW |
2011-05-20 09:00:00 |
Let’s update our blog text:
UPDATE blog SET content = 'Edited text' WHERE id = 1;
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.
Chetankumar Ravindra Patil 