How to Create Triggers in MySQL

Share this article

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:
  1. A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.
  2. The table which triggers the event. A single trigger can only monitor a single table.
  3. 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.
  4. 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:
idtitlecontentdeleted
1Article OneInitial text0
In addition, a new entry appears in our `audit` table:
idblog_idchangetypechangetime
11NEW2011-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:
idblog_idchangetypechangetime
11NEW2011-05-20 09:00:00
21EDIT2011-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:
idblog_idchangetypechangetime
11NEW2011-05-20 09:00:00
21EDIT2011-05-20 09:01:00
31DELETE2011-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.

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 BucklerCraig Buckler
View Author

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.

databaseeventsmysqltriggers
Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week