MySQL trigger easy introduction (with code examples)

The MySQL trigger is a special type of stored procedure that’s automatically executed by MySQL in response to an event.

A MySQL trigger can store SQL statements to execute when there’s an INSERT, UPDATE, or DELETE statement executed on a specific table.

The syntax to create a trigger looks as follows:

CREATE TRIGGER trigger_name    
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name 
  FOR EACH ROW
    BEGIN    
    --what to do here
    END;     

A trigger can select one of the 6 event types to determine when it should run:

• BEFORE or AFTER an INSERT statement is executed on the table
• BEFORE or AFTER an UPDATE statement is executed on the table
• BEFORE or AFTER a DELETE statement is executed on the table

While a MySQL trigger will be attached to one specific table when created, you can use the trigger to manipulate other tables beside the table the trigger is attached to.

Let’s see some examples of an INSERT trigger at work in MySQL.

Imagine that you has two tables named cities and population. Each table only have two columns as follows:

CREATE TABLE `cities` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` tinytext,
  PRIMARY KEY (`id`)  
);

CREATE TABLE `population` (
  `city_id` int unsigned NOT NULL AUTO_INCREMENT,
  `total_population` int DEFAULT NULL,
  PRIMARY KEY (`city_id`)
);

Each time a new city is inserted into the cities table, you want to also INSERT a new row on the population table, with the total_population field initialized at 10.

Here’s how you create a trigger that does that:

CREATE TRIGGER `after_insert_city` 
  AFTER INSERT ON cities
  FOR EACH ROW 
  INSERT INTO `population` VALUES (NEW.id, 10);

The trigger statement above specifies when the trigger should run and what it should do.

The AFTER INSERT ON cities statement will cause the trigger to run only after an INSERT statement has been executed on the cities table.

FOR EACH ROW that’s affected by the INSERT INTO cities statement, MySQL will run the trigger body that you specified.

In the above example, the trigger body is only executing a single INSERT statement to the population table.

With the trigger created, let’s perform some INSERT operations on the cities table:

INSERT INTO cities VALUES (NULL, "York");
INSERT INTO cities VALUES (NULL, "Bristol");
INSERT INTO cities VALUES (NULL, "Liverpool");

The trigger will automatically insert new rows to the population table as shown below:

+---------+------------------+
| city_id | total_population |
+---------+------------------+
|       1 |               10 |
|       2 |               10 |
|       3 |               10 |
+---------+------------------+

As you can see, the trigger works as expected and the population rows are initialized with ten total_population amount for each city.

Dropping the MySQL trigger

To stop the MySQL trigger from running each time you perform a data manipulation statement, you need to remove the trigger.

You can do so by running the DROP TRIGGER statement followed by the name of the trigger:

DROP TRIGGER `after_insert_city` 

The statement above should drop our example trigger.

The NEW and OLD keywords.

Going back to the trigger statement, you see a NEW keyword when inserting a new row to the population table as follows:

INSERT INTO `population` VALUES (NEW.id, 10);

MySQL provides you with the NEW and OLD keywords that are used to access columns in the rows affected by a trigger.

The NEW.id keyword means that you are using the value of the id field from the cities table as the value of city_id in the population table.

The availability of the NEW and OLD keywords depends on the trigger type you have in your table.

An INSERT trigger only has the NEW keyword available to access the new row values you inserted.

A DELETE trigger only has the OLD keyword available to access the row values that you’ve deleted.

Only the UPDATE trigger has both OLD and NEW keywords available. The OLD keyword is used to access the values before the update, and the NEW keyword after the update.

Executing multiple statements in a single trigger

A basic trigger can only execute one statement at a time.

If you want to create a complex trigger with multiple statements, then you need to add a DELIMITER clause and the BEGIN ... END compound statement.

Here’s an example of a trigger with two statements:

DELIMITER //
CREATE TRIGGER `after_city_insert` 
  AFTER INSERT ON cities
  FOR EACH ROW 
  BEGIN
    INSERT INTO `population` VALUES (NEW.id, 10);
    UPDATE `population` SET population = 25 WHERE city_id = NEW.id;
  END;//

By changing the DELIMITER from the default semicolon ; to a double slash //, you can pass the INSERT and UPDATE statements above as a whole.

Without the DELIMITER statement, then MySQL will interpret each statement above one by one, causing an error.

And that’s how you can create a trigger in a MySQL database server.

For more information, check out the official MySQL documentation for trigger examples.