MySQL 触发器的使用和理解

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name触发器的名称
tirgger_time触发时机，为BEFORE或者AFTER
trigger_event触发事件，为INSERT、DELETE或者UPDATE
tb_name表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
 执行语句列表
END

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 '姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`sort` int(11) NULL DEFAULT NULL COMMENT '排序字段',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

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;

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 ;

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

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

DELETE from stu where name = '李白'