提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
求助帖:MySQL创建触发器后插入数据报错
一、根据表tb_user创建了一个创建了触发器tb_user_insert_trigger
1.通过触发器记录tb_user表的数据变更日志,将变更日志插入到日志表user_logs表中,包含insert,update,delete
2.tb_user 表结构
CREATE TABLE `tb_user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(20) DEFAULT NULL COMMENT '姓名',
`phone` varchar(11) DEFAULT NULL COMMENT '电话号码',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`profession` varchar(20) DEFAULT NULL COMMENT '专业',
`age` int DEFAULT NULL COMMENT '年龄',
`gender` varchar(1) DEFAULT NULL COMMENT '性别,1为男,2为女',
`status` varchar(1) DEFAULT NULL COMMENT '状态',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
3.user_logs表结构
CREATE TABLE `user_logs` (
`id` int NOT NULL AUTO_INCREMENT,
`operation` varchar(20) NOT NULL COMMENT '操作类型,insert/update/delete',
`operate_time` datetime NOT NULL COMMENT '操作时间',
`operate_id` int NOT NULL COMMENT '操作的ID',
`operate_params` varchar(500) DEFAULT NULL COMMENT '操作参数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3```
4.触发器:tb_user_insert_trigger
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values(null,'insert',now(),new.id,concat('插入的数据内容为:id=',new.id,'name=',new.name,'phone=',new.phone,'email=',new.email,'profession=',new.profession,'age=',new.age,'gender=',new.gender,'status=',status,'createtime',new.createtime));
end;
5.向tb_user表中插入一行数据
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime)values(25,'嫦娥','17771234258','change@163.com','市场营销',26,'2','0',now());
6.报错:
删除触发器能正常插入,在这里请各位大佬帮忙看下是什么问题导致的,谢谢大家