mysql触发器怎么添加(MySQL触发器的使用场景及方法实例)
mysql触发器怎么添加
MySQL触发器的使用场景及方法实例触发器:
触发器的使用场景以及相应版本:
触发器可以使用的MySQL版本:
- 版本:MySQL5以上
使用场景例子:
- 每当增加一个顾客到某个数据库表时,都检查其电话号码格式是否正确,州的缩写是否为大写
- 每当订购一个产品时,都从库存数量中减去订购的数量
- 无论何时删除一行,都在某个存档表中保留一个副本
即:在某个表发生更改时自动处理。
如遇到触发器报错“Not allowed to return a result set from a trigger”;请划到最后看详解;
触发器的使用:
创建基本的触发器:
|
CREATE TRIGGER newproduct AFTER INSERT on products FOR EACH ROW BEGIN DECLARE msg VARCHAR (100); SET msg = "products added" ; SIGNAL SQLSTATE 'HY000' SET message_text = msg; END |
结果:
INSERT INTO products VALUES('demo2','1003','xiaoguo','66.6','hello world')
> 1644 - products added
> 时间: 0.035s
解释:
首先创建一个触发器:
|
#newproduct 触发器的名字 CREATE TRIGGER newproduct |
触发的时机:
BEFORE:触发器在触发他们的语句之前触发
AFTER:触发器在触发他们的语句完成后触发
在这里我们使用的after;也就是在插入结束后触发条件;
|
DECLARE msg VARCHAR (100); |
注意:declare语句是在复合语句中声明变量的指令;如果不声明msg,执行语句时,MySQL报错;
|
SIGNAL SQLSTATE 'HY000' SET message_text = msg; |
如果该SIGNAL语句指示特定SQLSTATE值,则该值用于表示指定的条件
"HY000”被称为“一般错误":
如果命令出现一般错误,则会触发后面的message中的消息;
注:该语句只是个人理解,也是一知半解,如果有更好的解释,欢迎留言。
触发的条件以BEGIN开始,END结束。
触发事件:
- insert
- update
- delete
删除触发器:
|
-- 删除触发器 DROP TRIGGER newproduct; |
INSERT触发器:
insert触发器在insert语句执行之前或者之后执行,需要注意以下几点:
- 在insert触发器代码内。可以引用一个名为NEW的虚拟表,访问被插入的行;
- 在before insert触发器中,NEW中的值也可以被更新(允许更改被插入的值)
- 对于AUTO_INCREMENT列,NEW在insert执行之前包含0,在insert执行之后包含新的自动生成值
例子:插入一个新的订单时,生成一个新的订单号保存到order_num
|
CREATE TRIGGER neworder AFTER INSERT ON orders for EACH ROW SELECT NEW.order_num into @ee; insert INTO orders(order_date,cust_id) VALUES (NOW(),10001); SELECT @ee as num; drop TRIGGER neworder; |
解释:
创建一个neworder的触发器,在插入之后执行,且对每个插入行执行,在insert中有一个与orders表一摸一样的虚表,用NEW 表示;
|
SELECT NEW.order_num into @a; |
在虚表中找到我们插入的数据的编号,将标号保存在a变量中;
检测:
|
insert INTO orders(order_date,cust_id) VALUES (NOW(),10001); SELECT @ee as num; |
插入数据,输出插入数据的编号
删除:
|
drop TRIGGER neworder; |
删除触发器。
例二:
在COURSE表上创建触发器,检查插入时是否出现课程名相同的记录,若有则不操作。
|
CREATE TRIGGER trg_course_in BEFORE INSERT ON course FOR EACH ROW BEGIN DECLARE msg VARCHAR (100); IF EXISTS ( SELECT * FROM course where cname=NEW.cname) THEN SET msg= '不能输入相同名称的课程' ; SIGNAL SQLSTATE 'HY000' SET message_text = msg; END IF; END |
例三:向student表中插入信息时,检查ssex的值必须为男或女。
|
CREATE TRIGGER trg_ssex AFTER INSERT on student FOR EACH ROW BEGIN DECLARE msg VARCHAR (100); IF(NEW.ssex not in ( '男' , '女' )) THEN SET msg = '性别必须为男或女' ; SIGNAL SQLSTATE 'HY000' SET message_text = msg; END IF END |
UPDATE触发器:
- 在update触发器的代码中,可以引用一个名为OLD的虚拟表访问以前的值,即:update未执行前的值,还可以引用一个名为NEW的虚拟表访问新更新的值;
- 在before update触发器中,NEW中的值可能也被更新(允许修改将要用于update语句中的值);
- OLD中的值全部只读,不能更新。
例一:保证州名缩写为大写
|
CREATE TRIGGER UPDATEevendor BEFORE UPDATE on vendors FOR EACH ROW SET new.vend_state = UPPER (new.vend_state); UPDATE vendors SET vend_state= 'hw' where vend_id= '1001' ; DROP TRIGGER UPDATEevendor; |
注:upper:将文本转换为大写:
例二:不允许修改student表中的学号sno,如果修改该列则显示错误信息并取消操作。
|
CREATE TRIGGER trg_student_updateSno BEFORE UPDATE FOR EACH ROW BEGIN DECLARE msg VARCHAR (100); IF NEW.sno <> OLD.sno THEN SET msg= '不允许修改sno' ; SIGNAL SQLSTATE 'HY000' SET message_text = msg; END IF; END |
DELETE触发器:
在DELETE触发器在delete语句执行之前或之后执行:
- 在delete触发器代码内,可以引用OLD的虚拟表,访问被删除的行;
- OLD中的值全部都是只读,不能更新
例子:
使用old保存将要被删除的行到一个存档表中
首先先创建一个与orders相似的表:
|
CREATE TABLE archive_orders LIKE orders; |
|
-- 创建一个删除的触发器 CREATE TRIGGER deleteorder BEFORE DELETE on orders for EACH ROW BEGIN INSERT INTO archive_orders(order_num,order_date,cust_id) VALUES (old.order_num,old.order_date,old.cust_id); END |
解释:
在删除order表中行中信息时,将删除的信息保存到archive_orders中;
删除原表中一行:
|
DELETE FROM orders WHERE order_num= '20014' ; |
查看效果:
|
SELECT * FROM archive_orders; |
结束:
注:如果遇到触发器报错“Not allowed to return a result set from a trigger”
- 原因:因为从MySQL5以后不支持触发器返回结果集
- 解决方法:在后面语句后面添加 into @变量名
- 取数据:select @变量名
详细解释:https://www.programmersought.com/article/3237975256/
创建用户变量:http://www.zzvips.com/article/148079.html
到此这篇关于MySQL触发器的使用场景及方法的文章就介绍到这了,更多相关MySQL触发器使用内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://www.cnblogs.com/xbhog/p/14111538.html
- mysql常见错误提示及解决办法(MYSQL 无法识别中文的永久解决方法)
- mysql根据子节点查询父节点(mysql 递归查找菜单节点的所有子节点的方法)
- mysql定时任务
- navicat15激活页面不显示(Navicat for MySQL 15注册激活详细教程)
- mysql的视图和临时表区别(MySQL 内存表和临时表的用法详解)
- mysql 操作系统时区(mysql时区查看与设置方法)
- mysql必知必会索引(MySQL索引知识的一些小妙招总结)
- 对mysql索引的理解(详解MySQL 8.0 之不可见索引)
- phpmysql完全学习手册教程(Windows下搭建PHP开发环境Apache+PHP+MySQL)
- mysql用什么类型数据(MySQL 数据类型选择原则)
- navicat配置远程访问mysql(解决Navicat无法连接 VMware中Centos系统中的 MySQL服务器的问题)
- mysql死锁修复思路(MySQL死锁检查处理的正常方法)
- idea向数据库中插入中文报错(Idea连接MySQL数据库出现中文乱码的问题)
- mysql5.5.36版本介绍(WDCP控制面板升级mysql为5.7.11的方法)
- mysql重复插入数据教程(Mysql避免重复插入数据的4种方式)
- mysql未使用索引的查询(如何在mysql进行查询缓存及失败的解决方法)
- 这里输入关键词(怎么输入关键词搜索)
- 得这个 难治病 的人太多了,300个人赶到杭州商量怎么办(得这个难治病的人太多了)
- 经度,世界时间腕表的灵魂(世界时间腕表的灵魂)
- 阿里最新财报公布 三季度营收增长3 ,将增加150亿美元回购额度 在美股价小涨(阿里最新财报公布)
- 赵薇时胖时瘦 最近变美少女 原因在这里 躺着就变瘦(赵薇时胖时瘦最近变美)
- 学会这26种姿势,你就可以和兵哥哥切磋了(你就可以和兵哥哥切磋了)
热门推荐
- angular教程第九讲(浅谈Angular的12个经典问题)
- 面试时问期望工资怎么回答
- python获取系统的utc时间(Python的UTC时间转换讲解)
- python中的多线程详解(python多线程抽象编程模型详解)
- mysql主从复制配置(Mysql实现主从配置和多主多从配置)
- html5按钮点击跳转(HTML5页面嵌入小程序没有返回按钮及返回页面空白的问题)
- svn查看某个用户的提交记录
- sqlserver并发性能(sql server中的任务调度与CPU深入讲解)
- python下载后依然打不开文件(解决python打不开文件文件不存在的问题)
- python中随机生成不重复随机数(python 在指定范围内随机生成不重复的n个数实例)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9