How to Create Triggers in MySQL

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:

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.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Eddie Shipman

    One very, very important thing you forgot to mention is that in MySQL, you need to have TRIGGER privileges and most shared hosts won’t give that to you. It is better to write SP’s to do the work for you instead.

    • Craig Buckler

      Thanks Eddie – you are absolutely correct – TRIGGER privileges are required. Most good hosts will allow you to set that, but it may be disabled by default.

      There is some overlap with triggers and stored procedures. They can both do similar jobs, but a trigger can be applied to an existing database without affecting back-end code.

      • Eddie Shipman

        The problem lies in the fact that, in my understanding, the trigger privilege applies to the server and that is why they normally do not allow it because they don’t want triggers written to manipulate DBs that don’t belong to your own. Also, many hosts haven’t yet upgraded to a version that doesn’t require SUPER to create triggers.

  • Eddie Shipman

    One very, very important thing you forgot to mention is that in MySQL, you need to have TRIGGER privileges and most shared hosts won’t give that to you. It is better to write SP’s to do the work for you instead.

  • Craig Buckler

    Thanks IT Metica.

    This is a simplified example and one of the main reasons I’ve used ON DELETE CASCADE is because it’s used in my following events post to physically delete all records from the blog and audit tables.

    In a live system, you could choose to implement ON DELETE RESTRICT and ON UPDATE RESTRICT to ensure no one can accidentally delete or update a blog record.

  • http://www.internationalpatentservice.com/index.html Trademark Application

    PHP and MySQL go together so why not a book on the two showing how they work together to create a website?

  • Anonymous

    Really useful article thanks

    I use to use triggers a lot in MS SQL, but have not really had chance to use them much in MySQL

    The syntax is mostly the same, just a few minor alternations, but those are the ones that keep getting me!

  • http://twitter.com/craigbuckler Craig Buckler

    Could you clarify why you think that? Triggers can be misused, sure, but the same is true of any technology.

  • http://blog.pathmakermarketing.com/blog/blog-services/ Blog Services

    Thanks for sharing on how to create mysql triggers. I kninda needed the info. Again, Thanks.

  • http://www.insurancegofer.com/auto Auto Insurance Online

    Thanks for the info… much appreciation

  • Kaf

    PLEASE DON’T USE TRIGGERS! If you need an event driven procedure in mysql then do it in your code.

    Imagine trying to maintain this!? You do an insert or delete and all of a sudden this other crap is happening! You pour through to code trying to find it but you never will!

    And how do you version control this when the logic changes? This is why business logic belongs in your code not in the DB.

    DO IT IN YOUR CODE! Your objects should have functions such as $object->save(); or $object->delete(). Add your trigger logic into those functions IN YOUR PHP! That way its in subversion (or whatever version control you are using) and its plainly there (and commented) for future developers to find.

    • Anonymous

      as long as you’re using it the way it is used here (to update audit tables) I don’t really see any harm, they is not part of your bussines rules.

    • Dana Byrd

      I appreciate the intensity of your comment. As someone who’s been to “where the heck is that update coming from hell”, ie trigger hell, I understand completely. The issue is that larger organizations often have teams of programmers using different languages for different projects. There are often different entry points to the corporate database that are beyond the scope of a single code base. In such situations triggers enforce corporate business logic universally, rather than expecting every programmer in the corporation to be omniscient, and work in perfect accord with one another.

      If you are using mysql a simple select can tell you if the menacing updates are coming from a trigger.
      select * from information_schema.triggers

      select * from information_schema.triggers where event_object_schema = ‘my database’ and event_object_table = ‘my table’;

      To keep a watchful eye on your tables/projects you can use queries to keep an eye out for a DBA that might unexpectedly add triggers on the tables you’re working with.
      select count(*) as trigger_cnt
      from information_schema.triggers where event_object_schema = ‘my database’;