MySQL triggers: creating and using triggers

MySQL triggers: creating and using triggers

This article uses examples to describe the creation and use of MySQL triggers. Share with you for your reference, the details are as follows:

We can use the CREATE TRIGGER statement to create a new trigger. Let's take a look at the specific syntax:

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON table_name
 FOR EACH ROW
 BEGIN
 ...
 END;

Then let's take a closer look at the specific meaning of the above SQL:

  • Place the trigger name after the CREATE TRIGGER statement. Trigger names should follow the naming convention [trigger time]_[table name]_[trigger event], for example before_employees_update.
  • The trigger activation time can be before or after. You must specify the activation time for the defined trigger. If you want to process the action before the change, then use the BEFORE keyword, if you need to process the action after the change, then use the AFTER keyword.
  • The triggering event can be INSERT, UPDATE or DELETE. This event causes the trigger to be called. A trigger can only be called by one event. To define a trigger that is called by multiple events, you must define multiple triggers, one for each event.
  • A trigger must be associated with a specific table. A trigger will not exist without a table, so the table name must be specified after the ON keyword.
  • Place SQL statements between BEGIN and END blocks. This is where the trigger logic is defined.

Now that we have a general understanding, let's try to create a trigger to record changes to row data in the employees table. Let's first look at the structure of this table:

mysql> DESC employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| extension | varchar(10) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| officeCode | varchar(10) | NO | MUL | NULL | |
| reportsTo | int(11) | YES | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
8 rows in set

Let's create a new table called employees audit to save changes to the data in the employees table:

CREATE TABLE employees_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employeeNumber INT NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  changedat DATETIME DEFAULT NULL,
  action VARCHAR(50) DEFAULT NULL
);

Next, create a BEFORE UPDATE trigger that is called before a row in the employees table is updated:

DELIMITER $$
CREATE TRIGGER before_employee_update 
  BEFORE UPDATE ON employees
  FOR EACH ROW 
BEGIN
  INSERT INTO employees_audit
  SET action = 'update',
   employeeNumber = OLD.employeeNumber,
    lastname = OLD.lastname,
    changedat = NOW(); 
END$$
DELIMITER ;

In the body of the above trigger, we have used the OLD keyword to access the employeeNumber and lastname columns of the rows affected by the trigger. We should note that in the trigger defined for insert, only the NEW keyword can be used. The OLD keyword cannot be used. However, in a trigger defined for a DELETE, there are no new rows, so you can only use the OLD keyword. In an update trigger, OLD refers to the row before the update, and NEW refers to the row after the update.

Then, we can use the SHOW TRIGGERS statement to view the triggers in the database:

mysql> SHOW TRIGGERS;
+------------------------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+------------------------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| before_employee_update | UPDATE | employees | BEGIN
  INSERT INTO employees_audit
  SET action = 'update',
   employeeNumber = OLD.employeeNumber,
    lastname = OLD.lastname,
    changedat = NOW();
END | BEFORE | 2017-08-02 22:06:36.40 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | utf8_general_ci |
+------------------------+--------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set

Once we're done we can update the employees table to check if the trigger was called:

UPDATE employees 
SET 
  lastName = 'Maxsu'
WHERE
  employeeNumber = 1056;

You can use the following query to query the employees_audit table to check whether the trigger is called by the UPDATE statement:

mysql> SELECT * FROM employees_audit;
+----+----------------+----------+---------------------+--------+
| id | employeeNumber | lastname | changedat | action |
+----+----------------+----------+---------------------+--------+
| 1 | 1056 | Hill | 2017-08-02 22:15:51 | update |
+----+----------------+----------+---------------------+--------+
1 row in set

As shown in the above output, the trigger is actually called and a new row is inserted into the employees_audit table.

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • MySQL trigger syntax and application examples
  • Detailed explanation of MySql view trigger stored procedure
  • MySQL trigger principle and usage example analysis
  • MySQL log trigger implementation code
  • MySQL trigger trigger add, delete, modify and query operation example
  • MySQL triggers: creating multiple triggers operation example analysis
  • Introduction to MySQL triggers, creation of triggers and analysis of usage restrictions
  • MySQL trigger definition and usage simple example
  • Detailed explanation of the concept, principle and usage of MySQL triggers
  • Tutorial on disabling and enabling triggers in MySQL [Recommended]
  • Detailed explanation of MySQL database triggers

<<:  JavaScript canvas realizes dynamic point and line effect

>>:  A few steps to easily build a Windows SSH server

Recommend

CSS horizontal centering and limiting the maximum width

A CSS layout and style question: how to balance h...

Detailed explanation of Excel parsing and exporting based on Vue

Table of contents Preface Basic Introduction Code...

How to use pdf.js to preview pdf files in Vue

When we preview PDF on the page, some files canno...

28 Famous Blog Redesign Examples

1. WebDesignerWall 2. Veerle's Blog 3. Tutori...

Automatically log out inactive users after login timeout in Linux

Method 1: Modify the .bashrc or .bash_profile fil...

React Hooks Usage Examples

Table of contents A simple component example More...

Nginx implements dynamic and static separation example explanation

In order to speed up the parsing of the website, ...

Three ways to use CSS inline styles, embedded styles, and external reference styles

A simple example of how to use the three methods ...

Tutorial on installing php5, uninstalling php, and installing php7 on centos

First, install PHP5 very simple yum install php T...

JavaScript to achieve fireworks effects (object-oriented)

This article shares the specific code for JavaScr...

The difference between traditional tomcat startup service and springboot startup built-in tomcat service (recommended)

Spring integration with springmvc The web.xml con...

Web project development JS function anti-shake and throttling sample code

Table of contents Stabilization Introduction Anti...

Summary of Linux nc command

NC's full name is Netcat (Network Knife), and...

Detailed tutorial on compiling and installing MySQL 5.7.24 on CentOS7

Table of contents Install Dependencies Install bo...

Let's talk in detail about the difference between unknown and any in TypeScript

Table of contents Preface 1. unknown vs any 2. Th...