Chapter 9: Triggers

 

In this chapter, we’ll talk about another interesting feature in MySQL - triggers.

 

What is a Trigger?

 

A trigger is a series of actions that is activated when a defined event occurs for a specific table. This event can either be an INSERT, UPDATE or DELETE. Triggers can be invoked before or after the event.

 

To understand how triggers work, let’s look at an example.

 

We’ll use the employees table to demonstrate.

 

Suppose one of the employees has just resigned from the company and we want to delete this employee from the employees table. However, before we do that, we would like to transfer the data into another table called ex_employees as a form of back up. We can do this using a trigger.

 

Let’s first create an ex_employees table using the code below:

 

CREATE TABLE ex_employees (

em_id INT PRIMARY KEY,

em_name VARCHAR(255) NOT NULL,

gender CHAR(1) NOT NULL,

date_left TIMESTAMP DEFAULT NOW()

);

 

Creating a Trigger

 

Next, we’ll use the following syntax to create our trigger (line numbers are added on the left for reference and are not part of the syntax):

 

1 DELIMITER $$

2

3 CREATE TRIGGER name_of_trigger BEFORE/AFTER UPDATE/DELETE/INSERT ON name_of_table FOR EACH ROW

4

5 BEGIN

6      -- Actions to take

7 END $$

8

9 DELIMITER ;

 

This syntax may look overwhelming at first. Do not worry, we’ll go over each keyword one by one.

 

On line 1, we have a new keyword - DELIMITER.

 

A delimiter is a character or sequence of characters that specifies the end of a SQL statement. Recall that previously, we have always used ; to specify the end of our SQL statements? We can change that if we want.

 

The code on line 1 (DELIMITER $$) tells MySQL that we want to use $$ as the delimiter for our CREATE TRIGGER statement (from lines 3 to 7).

 

Why do we need to do that?

 

The reason for...

Deleting a Trigger

 

Finally, let’s look at how we can delete an existing trigger.

 

To do that, we use the following syntax:

 

DROP TRIGGER [IF EXISTS] name_of_trigger;

 

To drop our update_ex_employees trigger, we write

 

DROP TRIGGER IF EXISTS update_ex_employees;