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.
