Use and understanding of MySQL triggers

1. What is a trigger?

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

My personal understanding is that it is a bit similar to Java's observer pattern. When an object changes, the observer also responds.

Mysql seems to support triggers from 5.0 onwards.

2. Create a trigger

I will introduce two ways to create triggers: creating with statements and creating with navicat.

The syntax for creating a trigger 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 trigger_time: the trigger time, which can be BEFORE or AFTER
trigger_event: trigger event, INSERT, DELETE or UPDATE
tb_name: indicates the table on which the trigger is created. trigger_stmt: the body of the trigger, which can be a SQL statement or multiple statements enclosed by BEGIN and END. So we can say that MySQL creates the following six types of 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 the trigger is executed, before or after the event.

FOR EACH ROW means that any operation on a record that meets the trigger event will trigger the trigger.

Create multiple triggers that execute statements:

CREATE TRIGGER trigger name BEFORE | AFTER trigger event ON table name FOR EACH ROW
BEGIN
 Execute statement list END

Use of NEW and OLD:

A field can be named new/lod.

Next we create two tables for testing:

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 a business where when data is added or deleted from the stu table, a log is synchronously recorded in stu_log, recording the name and time.

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

First we create an insert trigger using the statement:

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:

Step 1: Right-click the stu table and select Design Table ---- Trigger

Step 2: Fill in the options as shown in the figure and select Trigger before deletion

Step 3: Write the execution statement in the definition box below, as shown in the figure. Remember to click Save!

Statement:

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

3. Use triggers

Test it: add a new piece of data

insert into stu (name,age) VALUES('李白',36)

View the stu table and stu_log table:

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

Test deleting a piece of data

DELETE from stu where name = 'Li Bai'

View the stu table and stu_log table:

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

The above is the detailed content of using and understanding MySQL triggers. For more information about MySQL triggers, please pay attention to other related articles on 123WORDPRESS.COM!