The use of MySQL triggers and what to pay attention to

The use of MySQL triggers and what to pay attention to

About Triggers

In real development, we often encounter this situation. For example, when adding, deleting, and modifying information, we need to record logs. We have to write to the log table after completing the regular database logic operations, which becomes a two-step operation and is more complicated.

For example, when deleting a person's information, you need to delete his shopping records, delivery address, favorites, etc. This continuous operation is prone to errors, and consistency and integrity cannot be guaranteed. At this time, triggers should be used, which can not only avoid a lot of business logic code, but also better ensure data integrity.

A trigger is a database object related to a table: it triggers an action when the defined conditions are met and executes the set of statements defined in the trigger. This feature of triggers can help applications ensure data integrity on the database side.

It is a special stored procedure associated with a table event, which is activated and executed when an operation (insert, delete, update) is performed on a table.

Use of triggers

Create a trigger

The syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event ON t_name FOR EACH ROW trigger_stmt

illustrate:

trigger_name: trigger name

tirgger_time: trigger execution time point, before data operation (BEFORE) or after data operation (AFTER)

trigger_event: trigger event, add (INSERT), delete (DELETE), change (UPDATE)

t_name: refers to creating a trigger on the t_name table

trigger_stmt: The body of the trigger, which can be a SQL statement or multiple statements enclosed by BEGIN and END

Triggers can only be created on permanent tables (Permanent), not on temporary tables (Temporary)

FOR EACH ROW fixed expression, indicating that any operation on a record that satisfies the trigger event will trigger the trigger

There are two types of trigger_time and three types of trigger_event, so there are 6 combinations in total: BEFORE INSERT, BEFORE DELETE, BEFORE UPDATE, AFTER INSERT, AFTER DELETE, AFTER UPDATE

Example (first create a log table to fill in the value when the trigger is triggered):

/*First create a log table to fill in the value when the trigger is triggered*/
mysql> DROP TABLE IF EXISTS `TriggerLog`;
Query OK, 0 rows affected

mysql> CREATE TABLE `TriggerLog`
(
  `id` INT auto_increment PRIMARY KEY,
  `trigger_time` VARCHAR(30),
  `trigger_event` VARCHAR(30),
  `memo` VARCHAR(200)
);
Query OK, 0 rows affected

Insert type trigger:

mysql>
/*Here the SQL script is declared to end as // */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_insert;
CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END //
Query OK, 0 rows affected

mysql>
/*Reset the end of the SQL script to; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*Insert a piece of data*/
insert into students(studentname,score,classid) values('trigger1',100,0);
Query OK, 1 row affected


mysql>
/*Query the log table to see if there is a trigger write*/
select * from `TriggerLog`;
+----+--------------+---------------+------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+------------------------+
| 1 | after | insert | new student info,id:21 |
+----+--------------+---------------+------------------------+
1 row in set

Update type trigger:

mysql>
/*Here the SQL script is declared to end as // */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_update;
CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','update',concat('update student info,id:',cast(new.studentid as char)));
END //
Query OK, 0 rows affected

mysql>
/*Reset the end of the SQL script to; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*Update data*/
update students set score=99 where studentname='trigger1';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
/*Query the log table to see if there is a trigger write when updating*/
select * from `TriggerLog`;
+----+--------------+---------------+---------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+---------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
+----+--------------+---------------+---------------------------+
2 rows in set

Delete type trigger:

mysql>
/*Here the SQL script is declared to end as // */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_delete;
CREATE TRIGGER trig_after_delete AFTER DELETE ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','update',concat('delete student info,id:',cast(old.studentid as char)));
END //
Query OK, 0 rows affected

mysql>
/*Reset the end of the SQL script to; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/* delete data */
delete from students where studentid=21;
Query OK, 1 row affected

mysql>
/*Query the log to see if the deletion triggers a write*/
select * from `TriggerLog`;
+----+--------------+---------------+---------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+---------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
| 3 | after | update | delete student info,id:21 |
+----+--------------+---------------+---------------------------+
3 rows in set

View Triggers

View All Triggers

show triggers; --Syntax
mysql> show triggers;
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+--------------------+
| trig_after_insert | INSERT | students | BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
| trig_after_update | UPDATE | students | BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','update',concat('update student info,id:',cast(new.studentid as char)));
END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
| trig_after_delete | DELETE | students | BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','update',concat('delete student info,id:',cast(old.studentid as char)));
END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+--------------------+
3 rows in set

View the creation statement of the trigger

show create trigger trigger_name; --Syntax
mysql> show create trigger trig_after_insert;
+-------------------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation |
+-------------------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| trig_after_insert | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------------+--------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set

Deleting a trigger

drop trigger trigger_name; --Syntax
mysql> drop trigger trig_after_insert;
Query OK, 0 rows affected
 
mysql> show create trigger trig_after_insert;
1360 - Trigger does not exist

Notes on use

The difference between NEW and OLD

The trigger targets each row of records in the database. Each row of data will have a corresponding state before and after the operation. The trigger saves the state before the operation to the old keyword and saves the state after the operation to the new keyword.

 new.cname -- a column of data in the newly added row (or the row before modification) old.cname -- a column of data in the deleted row (or the row after modification)

It should be noted that not all triggers have old and new.

Trigger Type Use of new and old
INSERT trigger There is no old, only new, new means the data that will be added (before insertion) or has been added (after insertion)
UPDATE trigger There are both old and new. Old represents the data before the update, and new represents the data after the update.
DELETE trigger There is no new, only old. Old indicates data that is about to be deleted (before deletion) or has been deleted (after deletion).

In fact, we have already used new/old to create triggers and obtain data. Let's modify the update trigger (trig_after_update) based on this table and compare the output before and after the modification:

mysql>
/*Here the SQL script is declared to end as // */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_update;
CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ​​('after','update',concat('from:',old.studentname,',',old.score,' ','to:',new.studentname,',',new.score));
END //
Query OK, 0 rows affected

mysql>
/*Reset the end of the SQL script to; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*Update grades and names*/
update students set score=106,studentname='trigger2' where studentid=17;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
/*Compare the values ​​before and after the update based on old and new*/
select * from `TriggerLog`;
+----+--------------+---------------+--------------------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+--------------------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
| 3 | after | update | delete student info,id:21 |
| 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
+----+--------------+---------------+--------------------------------------+
4 rows in set

Unable to trigger modifications to the same table

The MySQL trigger cannot perform insert, update, or delete operations on this table, otherwise an error will be reported.

mysql>
/*Here the SQL script is declared to end as // */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_insert;
CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
 update students set score = score+1 where studentid= new.studentid;
END //
Query OK, 0 rows affected

mysql>
/*Reset the end of the SQL script to; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*After inserting a piece of data, an error is reported because the modification of this table is triggered*/
insert into students(studentname,score,classid) values('trigger2',101,0);
1442 - Can't update table 'students' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

summary

1. Triggers can implement cascading changes through associated tables in the database, that is, changes in the data in one table will affect the data in other tables, such as data modification, data statistics, data replication, etc.
2. It can ensure data security, perform security checks, and limit users' rights to operate the database.
3. Data integrity checks and constraints can be performed on the implementation of complex logic.
4. Triggers should be used only when needed. If you rely too much on triggers, it will affect the structure of the database and increase the execution and maintenance costs of the database.
5. Triggers are divided into BEFORE triggers and AFTER triggers, so its execution steps are to execute the BEFORE trigger first, then execute the business script, and finally execute the AFTER trigger. It should be noted that if a step fails, it will no longer be executed. If it is a transaction table, it will be rolled back. If it is a non-transaction table, it cannot be rolled back and the data may be inconsistent.

Two limitations of triggers

1. Triggers cannot call stored procedures that return data to the client, nor can they use dynamic SQL statements that use the CALL statement. However, stored procedures are allowed to return data to triggers through parameters. That is, stored procedures or functions can return data to triggers through OUT or INOUT type parameters, but procedures that directly return data cannot be called.
2. Statements that start or end transactions explicitly or implicitly, such as START TRANS-ACTION, COMMIT, or ROLLBACK, cannot be used in triggers.

The above is the detailed content of the use of MySQL triggers and the things that need to be paid attention to. For more information about MySQL triggers, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • Detailed explanation of mysql trigger example
  • Introduction and use of triggers and cursors in MySQL
  • Use and understanding of MySQL triggers
  • How to use cursor triggers in MySQL
  • MySQL trigger usage scenarios and method examples
  • Detailed explanation of MySQL database triggers
  • A brief discussion on MySql views, triggers and stored procedures
  • mysql trigger creation and usage examples
  • Detailed explanation of the basic usage of MySQL triggers [create, view, delete, etc.]
  • Use of MySQL triggers

<<:  Example of using CSS to achieve semi-transparent background and opaque text

>>:  js to implement file upload style details

Recommend

MySQL performance optimization: how to use indexes efficiently and correctly

Practice is the only way to test the truth. This ...

MySQL 8.0.21 installation and configuration method graphic tutorial

Record the installation and configuration method ...

How to configure Jupyter notebook in Docker container

Jupyter notebook is configured under the docker c...

The unreasonable MaxIdleConns of MySQL will cause short connections

1 Background Recently, some performance issues ha...

The latest popular script Autojs source code sharing

Today I will share with you a source code contain...

Four categories of CSS selectors: basic, combination, attribute, pseudo-class

What is a selector? The role of the selector is t...

Set an icon for the website to be displayed on the far left of the browser tab

What is the purpose of this sentence? Copy code Th...

Basic usage of @Font-face and how to make it compatible with all browsers

@Font-face basic introduction: @font-face is a CSS...

Use of docker system command set

Table of contents docker system df docker system ...

How to hide the version number in Nginx

Nginx hides version number In a production enviro...

Detailed explanation of the use of grid properties in CSS

Grid layout Attributes added to the parent elemen...

MySQL FAQ series: When to use temporary tables

Introduction to temporary tables What is a tempor...

A brief analysis of the function calling process under the ARM architecture

Table of contents 1. Background knowledge 1. Intr...

mysql executes sql file and reports error Error: Unknown storage engine'InnoDB' solution

Find the problem I recently encountered a problem...

Analysis of the process of deploying pure HTML files in Tomcat and WebLogic

1. First, the pure HTML file must have an entry i...