Introduction to MySQL triggers

Foreword:

In the process of learning MySQL, you may have understood the concept of triggers, and it is not clear whether you have studied triggers in detail. Recently, I have read a few documents about triggers to share the relevant knowledge of MySQL triggers.

1. Introduction to triggers

Triggers are triggers, which are database objects related to the table, which are triggered when the defined conditions are met, and execute the set of statements defined in the trigger. Its execution is not called by the program, nor is it started manually, but triggered by events, such as when an operation (insert, delete, update) is performed on a table, it will be activated for execution. Triggers are often used to strengthen data integrity constraints and business rules.

Refer to the official document, the trigger creation syntax template is as follows:

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

Triggers can only be created on permanent tables. Triggers cannot be created on temporary tables or views. The name of the trigger is unique within a single database. Referring to the above creation statement, there are several elements of trigger creation, which are briefly described below:

trigger_time : is the trigger action time, which can be BEFORE or AFTER, indicating that the trigger is activated before or after each row to be modified.

trigger_event : Indicates the operation type of the activation trigger. These trigger_event values are allowed:

• insert: As long as a new row is inserted into the table, the trigger will activate. For example, insert, load data, and replace statements.
• update: The trigger is activated when a row of data in the table is changed. For example, the update statement.
• delete: The trigger is activated when a row of data is deleted from the table. For example, delete and replace statements. The DROP TABLE and TRUNCATE TABLE statements on the table will not activate this trigger, because they do not use delete, and deleting a partition will not activate the delete trigger.

trigger_body : is the statement to be executed when the trigger is activated. If you want to execute multiple statements, you can use the BEGIN...END compound statement structure. In the trigger body, you can use old and new to refer to the record content that has changed in the trigger.

2. Trigger specific operations

Let's look at the specific operations related to triggers:

# 创建表 创建触发器
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
       FOR EACH ROW
       BEGIN
           IF NEW.amount < 0 THEN
               SET NEW.amount = 0;
           ELSEIF NEW.amount > 100 THEN
               SET NEW.amount = 100;
           END IF;
       END;//
mysql> delimiter ;

# 验证触发器作用
mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |   14.98 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)

mysql> update account set amount = 114.98 where acct_num = 137;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
3 rows in set (0.00 sec)

# 查看触发器
mysql> show triggers;

# 删除触发器
mysql> drop trigger if exists upd_check;

# 查看数据库实例中所有触发器
SELECT
    a.TRIGGER_SCHEMA,
    a.TRIGGER_NAME,
    a.ACTION_TIMING,
    a.EVENT_OBJECT_TABLE,
    a.EVENT_MANIPULATION 
FROM
    information_schema.`TRIGGERS` a 
WHERE
    a.TRIGGER_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
  
delimiter // 设置MySQL执行结束标志，默认为;

The above shows some basic operations on triggers. In fact, triggers are relatively rare in the production environment. Even if it can solve some of our database needs, because the use of triggers has a series of shortcomings, a brief summary of the shortcomings is as follows :

• It is difficult to locate the business logic implemented using triggers when problems occur, especially when multiple triggers are involved, which will make later maintenance difficult.
• Extensive use of triggers can easily cause the code structure to be disrupted and increase the complexity of the program.
• If the amount of data that needs to be changed is large, the execution efficiency of the trigger will be very low.
• The implicit call of the trigger is easy to be ignored, and the problem is not easy to troubleshoot.

But triggers are not useless. For example, if we don't want people to delete or update the data in this table, we can use triggers. The following scenarios may inspire you:

# 禁止删除数据 即使你有权限
mysql> select * from student;
+--------------+------+--------+-------+-------+
| increment_id | s_id | s_name | s_sex | s_age |
+--------------+------+--------+-------+-------+
|            1 | 1001 | sdfsd  | 男    |    18 |
|            2 | 1003 | zsdfsd | 女    |    19 |
+--------------+------+--------+-------+-------+
2 rows in set (0.00 sec)

mysql> delimiter //
mysql> CREATE TRIGGER `tri_delstu` BEFORE DELETE ON `student` FOR EACH ROW begin
    -> declare msg varchar(255);
    -> set msg="不允许删除学生信息";
    -> SIGNAL SQLSTATE 'HY000' SET  MESSAGE_TEXT = msg;
    -> end; //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> delete from student where s_id = 1003;
ERROR 1644 (HY000): 不允许删除学生信息

# 禁止更新某个字段
mysql> delimiter //
mysql> CREATE TRIGGER trg__updateSid BEFORE UPDATE ON `student`
    -> FOR EACH ROW
    -> BEGIN
    ->  DECLARE msg VARCHAR(100); 
    ->  IF NEW.s_id <> OLD.s_id THEN
    ->  SET msg='不允许修改学号'; 
    ->  SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
    ->  END IF; 
    -> END; //
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> update student set s_id = 1002 where increment_id = 2;
ERROR 1644 (HY000): 不允许修改学号

# 限制修改范围
mysql> delimiter //
mysql> CREATE TRIGGER `tri_update_age` BEFORE UPDATE ON `student` FOR EACH ROW BEGIN
    ->         DECLARE msg VARCHAR(20);
    ->   IF (NEW.s_age<0) THEN 
    ->         set msg="年龄不能小于0";
    ->   signal sqlstate 'HY000' set message_text=msg;
    ->         END IF;
    -> END; //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> update student set s_age=10 where s_id = 1001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update student set s_age=-10 where s_id = 1001;
ERROR 1644 (HY000): 年龄不能小于0

summary:

This article briefly introduces the definition and use cases of triggers. Triggers are not recommended in systems with complex business logic or frequent table changes. Of course, it also has its own application scenarios. No matter what, the logic of triggers is always as simple as possible. We should let the database do what it is good at. It's a matter of thinking that all logic is implemented at the database level.