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

Detailed explanation of installing applications in Linux Centos7 without Internet connection

1. In the previous chapter, we learned that we ca...

Tutorial on installing Odoo14 from source code on Ubuntu 18.04

Table of contents Background of this series Overv...

The JS hasOwnProperty() method detects whether a property is an object's own property.

The JavaScript hasOwnProperty() method is the pro...

mysql query data from one table and insert it into another table implementation method

mysql query data from one table and insert it int...

JavaScript to achieve simple provincial and municipal linkage

This article shares the specific code for JavaScr...

Detailed process of SpringBoot integrating Docker

Table of contents 1. Demo Project 1.1 Interface P...

Solution to 1045 error when navicat connects to mysql

When connecting to the local database, navicat fo...

How to configure MySQL scheduled tasks (EVENT events) in detail

Table of contents 1. What is an event? 2. Enable ...

HTML+CSS+JavaScript to make a girlfriend version of scratch card (you will learn it once you see it)

I believe everyone has played scratch tickets. Wh...

Windows Server 2019 IIS10.0+PHP(FastCGI)+MySQL Environment Construction Tutorial

Preparation 1. Environmental Description: Operati...

A brief discussion on MySQL event planning tasks

1. Check whether event is enabled show variables ...

Why Google and Facebook don't use Docker

The reason for writing this article is that I wan...

How to install and use Ubuntu Docker

Table of contents 1. Automatic installation using...

Solve the problem of insufficient docker disk space

After the server where Docker is located has been...

React Hook usage examples (6 common hooks)

1. useState: Let functional components have state...