preface:

In the process of learning mysql, you may have understood the concept of triggers. It is not clear whether you have studied triggers in detail. Recently, I read several documents about triggers and shared MySQL trigger related knowledge.

1. Introduction to trigger

triggers are database objects related to tables. They trigger when the defined conditions are met, and execute the statement set defined in the trigger. Its execution is not called by the program or started manually, but triggered by events. For example, when a table is operated (insert, delete, update), it will be activated for execution. triggers are often used to enforce data integrity constraints and business rules.

Referring 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, trigger creation has several elements, 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 line to be modified.

trigger_event: indicates the type of action that activates the trigger. These triggers_ Event value is allowed:

• Insert: the trigger is activated whenever a new row is inserted into the table. For example, insert, load data, replace statements.
• Update: activates the trigger when a row of data in the table is changed. For example, update statement.
• Delete: activates the trigger when a row of data is deleted from the table. For example, delete and replace statements. DROP TABLE and TRUNCATE TABLE statements on tables do not activate this trigger because they do not use delete, and deleting partitions does 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 changed records in the trigger.

2. Specific operation of trigger

Here are the specific operations related to triggers:

# Create table create trigger
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 ;

# Verify trigger action
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)

# View triggers
mysql> show triggers;

# Delete trigger 
mysql> drop trigger if exists upd_check;

# View all triggers in the database instance
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 // Set the MySQL execution end flag. The default is;

The above shows some basic operations about triggers. In fact, triggers are relatively rare in the production environment. Even if they can solve some database requirements, there are a series of disadvantages in the use of triggers. A brief summary of the disadvantages is as follows:

• The business logic implemented with triggers is difficult to locate when problems occur, especially when multiple triggers are involved, which will make later maintenance difficult.
• The extensive use of triggers can easily lead to the disorder of code structure and increase the complexity of the program,
• If the amount of data to be changed is large, the execution efficiency of the trigger will be very low.
• The implicit call of trigger is easy to be ignored, and it is difficult to troubleshoot problems.

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

# It is forbidden to delete data even if you have permission
mysql> select * from student;
+--------------+------+--------+-------+-------+
| increment_id | s_id | s_name | s_sex | s_age |
+--------------+------+--------+-------+-------+
|            1 | 1001 | sdfsd  | male    |    18 |
|            2 | 1003 | zsdfsd | female    |    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="Deleting student information is not allowed";
    -> 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): Deleting student information is not allowed

# Prohibit updating a field
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='Student number cannot be modified'; 
    ->  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): Student number cannot be modified

# Limit the scope of modification
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="Age cannot be less than 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): Age cannot be less than 0

Summary:

This article briefly introduces the definition and use cases of trigger. Triggers are not recommended in systems with complex business logic or frequent table changes. Of course, they also have their own application scenarios. In any case, the simpler the logic of triggers, the better. We should let the database do what it is good at. We can't think that all logics are implemented at the database level.