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

1 minute Vue implements right-click menu

Table of contents Rendering Install Code Implemen...

Solution to the problem that MySQL commands cannot be entered in Chinese

Find the problem Recently, when I connected to th...

Analysis of the Neglected DOCTYPE Description

doctype is one of them: <!DOCTYPE HTML PUBLIC &...

Install and configure MySQL under Linux

System: Ubuntu 16.04LTS 1\Download mysql-5.7.18-l...

Detailed explanation of MySQL cumulative calculation implementation method

Table of contents Preface Demand Analysis Mysql u...

HTML tags list and usage instructions

List of HTML tags mark type Name or meaning effec...

Vue implements the browser-side code scanning function

background Not long ago, I made a function about ...

HTML code to add quantity badge to message button

HTML code: <a onclick="goMessage();"...

2 methods and precautions for adding scripts in HTML

How to add <script> script in HTML: 1. You c...

Docker practice: Python application containerization

1. Introduction Containers use a sandbox mechanis...

MySQL installation and configuration methods and precautions under Windows platform

2.1、msi installation package 2.1.1、Installation I...

Summary of using the reduce() method in JS

Table of contents 1. Grammar 2. Examples 3. Other...