MySQL — on the use and understanding of triggers

Time:2022-5-22

This article introduces triggers in three steps:

1. What is a trigger

2. Create trigger

3. Use trigger


 

1. What is a trigger?

A special kind of database program that can monitor the operation of certain data (insert / update / delete) and trigger relevant operations (insert / update / delete) to protect the integrity of data.

Personal understanding is a bit similar to the observer mode of Java. When an object changes, the observer responds accordingly.

MySQL seems to support triggers since 5.0.

 

2. Create trigger

To create a trigger, I will introduce two ways: create it with statement and create it with Navicat.

The syntax for creating triggers is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_ Name: the name of the trigger
tirgger_ Time: trigger time, before or after
trigger_ Event: trigger event, which is insert, delete or update
tb_ Name: indicates the table on which the trigger is created
trigger_ Stmt: the program body of the trigger, which can be an SQL statement or multiple statements contained in begin and end
Therefore, MySQL can create the following six triggers:
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

The trigger name parameter refers to the name of the trigger to be created

The before and after parameters specify when execution is triggered, before or after the event

For each row means that the trigger event will be triggered if the operation on any record meets the trigger event

Create multiple triggers to execute statements:

Create trigger trigger name before | after trigger event
On table name for each row
BEGIN
    Execution statement list
END

Use of new and old:

Trigger Type Use of new and old
insert New stands for new data
update New represents the updated data, and old represents the data before the update
delete Old represents the data to be deleted

A field can be used as new / LOD Field name

Next, we create two tables to test:

Stu table: main table (observed)

DROP TABLE IF EXISTS `stu`;
CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_ general_ Ci null default null comment 'name',
`Age ` int (11) null default null comment 'age',
`Sort ` int (11) null default null comment 'sort field',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

stu_ Log table: trigger association table (observer)

DROP TABLE IF EXISTS `stu_log`;
CREATE TABLE `stu_log` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

Now let’s assume that there is such a business. When adding and deleting data in the stu table, it is synchronized in the stu_ Log to record the name and time.

If we don’t use triggers, we need to write code to implement this requirement, but triggers can help us easily.

First, we use the statement to create an insert trigger:

DROP TRIGGER IF EXISTS `insert_log`;
delimiter ;;
CREATE TRIGGER `add_log` AFTER INSERT ON `stu` FOR EACH ROW BEGIN
INSERT INTO stu_log(name,create_time)  VALUES(new.`name`,now());
END
;;
delimiter ;

 

Execution result:

Then we use Navicat to create a delete trigger:

Step1: right click the stu table and select design table – trigger

 

Step 2: fill in and select as shown in the figure. Trigger before deleting

 

Step3: write the execution statement in the definition box below, as shown in the figure. Remember to click Save!

 

sentence:

begin
INSERT INTO stu_log(name,create_time)  VALUES(old.`name`,now());
end

 

3. Use trigger

Test: add a new piece of data

Insert into stu (name, age) values ('li Bai ', 36)

View stu table and stu_ Log table:

 

As shown in the figure, the trigger has taken effect!

 

Test delete a piece of data

Delete from stu where name = 'Li Bai'

 

View stu table and stu_ Log table:

 

 

 

As shown in the figure, the trigger has taken effect!

 

In this article, we will discuss the trigger. We can use the trigger to realize more functions, such as log audit, synchronous replication table, automatic calculation and so on. Welcome to leave a message!

 

 

May you always have surprises in every ordinary day.