MySQL 版本5.7.24社区版
1. 需求
测试触发器功能。现有用户表users和日志表logs。需实现当向用户表新增记录时，由触发器自动向日志表插入该用户的创建日志，内容为“users.name is created”。
2. 建表语句如下：
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
`add_time` datetime DEFAULT current_timestamp,
PRIMARY KEY (`id`),
KEY `name` (`name`(250)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1000013 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `logs` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`log` varchar(255) DEFAULT NULL COMMENT '日志说明',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='日志表'；
3.触发器创建语句：
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
delimiter ;;
drop trigger if exists tri_user_log_test4;
CREATE DEFINER=`DBA_yanmingzhun`@`192.168.1.%` TRIGGER tri_user_log_test4 AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(60)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集
SET s2 = " is created at ";
SET s1 = CONCAT(NEW.name,s2,new.add_time); #函数CONCAT可以将字符串连接
INSERT INTO logs(log) values(s1);
END
;;
delimiter ;
4.效果检查
向用户表插入两条记录：
insert into users(name) values ('Jully Chen'),('James Wang');
查看用户表插入记录情况：
mysql> select * from users;
+----+------------+---------------------+
| id | name | add_time |
+----+------------+---------------------+
| 1 | Jully Chen | 2019-11-22 10:59:20 |
| 2 | James Wang | 2019-11-22 10:59:20 |
+----+------------+---------------------+
2 rows in set (0.00 sec)
查看触发器向日志表插入记录情况：
mysql> select * from logs;
+----+----------------------------------------------+
| Id | log |
+----+----------------------------------------------+
| 1 | Jully Chen is created at 2019-11-22 10:59:20 |
| 2 | James Wang is created at 2019-11-22 10:59:20 |
+----+----------------------------------------------+
2 rows in set (0.00 sec)
5.总结
trigger_event为 INSERT | UPDATE 时，通过new.字段名引用tbl_name表字段值；
trigger_event为UPDATE | DELETE 时，通过old.字段名引用tbl_name 表字段值；
trigger_order指定触发顺序可以应用多个触发器。
trigger_body中可以操作数据库实例中其它相关表。
delimiter:end-of-statement delimiter
