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.
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.
- 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.
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 |
id | blog_id | changetype | changetime |
---|---|---|---|
1 | 1 | NEW | 2011-05-20 09:00:00 |
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 |
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 |
Frequently Asked Questions (FAQs) about MySQL Triggers
What is the purpose of using MySQL Triggers?
MySQL Triggers are a type of stored program that automatically executes or fires when a specific event occurs in the database. They are used to maintain the integrity of the data in the database. Triggers can be used to perform a variety of tasks such as logging, enforcing business rules, replicating data, and other administrative tasks.
How do I create a MySQL Trigger?
To create a MySQL Trigger, you need to use the CREATE TRIGGER statement. This statement includes the name of the trigger, the triggering event, and the statement to execute when the trigger fires. Here is a basic example:CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name FOR EACH ROW
BEGIN
-- SQL statements
END;
What are the different types of MySQL Triggers?
There are three types of MySQL Triggers: BEFORE INSERT, AFTER INSERT, and INSTEAD OF INSERT. BEFORE INSERT triggers are fired before the insert operation is executed. AFTER INSERT triggers are fired after the insert operation is executed. INSTEAD OF INSERT triggers replace the insert operation.
Can I have multiple triggers for the same event on a table?
Yes, you can have multiple triggers for the same event on a table. However, the order in which they are fired is determined by the order in which they were created.
How do I view the triggers in my database?
You can view the triggers in your database by querying the INFORMATION_SCHEMA.TRIGGERS table. Here is an example:SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_database';
How do I delete a trigger in MySQL?
To delete a trigger in MySQL, you use the DROP TRIGGER statement followed by the name of the trigger. Here is an example:DROP TRIGGER trigger_name;
Can I use a trigger to modify the data being inserted into a table?
Yes, you can use a trigger to modify the data being inserted into a table. This is typically done using a BEFORE INSERT trigger.
Can I call a stored procedure from a trigger?
Yes, you can call a stored procedure from a trigger. However, you should be careful when doing this as it can lead to complex dependencies and unexpected results.
What happens if a trigger causes an error?
If a trigger causes an error, the operation that caused the trigger to fire is rolled back, and the error is returned to the client.
Can I use a trigger to enforce business rules?
Yes, you can use a trigger to enforce business rules. For example, you could use a trigger to prevent a record from being deleted if it has related records in another table.
Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.