mysql 触发器语法与应用示例
程序员文章站
2022-11-22 15:51:01
本文实例讲述了mysql 触发器语法与应用。分享给大家供大家参考,具体如下:例子:创建触发器,记录表的增、删、改操作记录//创建user表;drop table if exists `user`;cr...
本文实例讲述了mysql 触发器语法与应用。分享给大家供大家参考,具体如下:
例子:创建触发器,记录表的增、删、改操作记录
//创建user表; drop table if exists `user`; create table `user` ( `id` bigint(20) not null auto_increment, `account` varchar(255) default null, `name` varchar(255) default null, `address` varchar(255) default null, primary key (`id`) ) engine=innodb default charset=utf8; //创建对user表操作历史表 drop table if exists `user_history`; create table `user_history` ( `id` bigint(20) not null auto_increment, `user_id` bigint(20) not null, `operatetype` varchar(200) not null, `operatetime` datetime not null, primary key (`id`) ) engine=innodb default charset=utf8;
delimiter:改变输入的结束符,默认情况下输入结束符是分号;,这里把它改成了两个分号;;,这样做的目的是把多条含分号的语句做个封装,全部输入完之后一起执行,而不是一遇到默认的分号结束符就自动执行;
- new:当触发插入和更新事件时可用,指向的是被操作的记录
- old: 当触发删除和更新事件时可用,指向的是被操作的记录
insert:
drop trigger if exists `tri_insert_user`; delimiter ;; create trigger `tri_insert_user` after insert on `user` for each row begin insert into user_history(user_id, operatetype, operatetime) values (new.id, 'add a user', now()); end ;; delimiter ;
update:
drop trigger if exists `tri_update_user`; delimiter ;; create trigger `tri_update_user` after update on `user` for each row begin insert into user_history(user_id,operatetype, operatetime) values (new.id, 'update a user', now()); end ;; delimiter ;
delete:
drop trigger if exists `tri_delete_user`; delimiter ;; create trigger `tri_delete_user` after delete on `user` for each row begin insert into user_history(user_id, operatetype, operatetime) values (old.id, 'delete a user', now()); end ;; delimiter ;
上一篇: 2020.10.10 第四课
下一篇: MySQL分区表的最佳实践指南