MySQL触发器的使用场景及方法实例

2023-07-08 12:17:03 73

触发器：

触发器的使用场景以及相应版本：

触发器可以使用的MySQL版本：

版本：MySQL5以上

使用场景例子：

每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写
每当订购一个产品时，都从库存数量中减去订购的数量
无论何时删除一行，都在某个存档表中保留一个副本

即：在某个表发生更改时自动处理。

如遇到触发器报错“Notallowedtoreturnaresultsetfromatrigger”；请划到最后看详解；

触发器的使用：

创建基本的触发器：

CREATETRIGGERnewproductAFTERINSERTonproductsFOREACHROW
BEGIN
DECLAREmsgVARCHAR(100);
SETmsg="productsadded";
SIGNALSQLSTATE'HY000'SETmessage_text=msg;

END

结果：

INSERTINTOproductsVALUES('demo2','1003','xiaoguo','66.6','helloworld')
>1644-productsadded
>时间:0.035s

解释：

首先创建一个触发器：

#newproduct触发器的名字
CREATETRIGGERnewproduct

触发的时机：

BEFORE：触发器在触发他们的语句之前触发

AFTER：触发器在触发他们的语句完成后触发

在这里我们使用的after;也就是在插入结束后触发条件；

DECLAREmsgVARCHAR(100);

注意：declare语句是在复合语句中声明变量的指令；如果不声明msg,执行语句时，MySQL报错；

SIGNALSQLSTATE'HY000'SETmessage_text=msg;

如果该SIGNAL语句指示特定SQLSTATE值，则该值用于表示指定的条件

"HY000”被称为“一般错误"：

如果命令出现一般错误，则会触发后面的message中的消息;

注：该语句只是个人理解，也是一知半解，如果有更好的解释，欢迎留言。

触发的条件以BEGIN开始，END结束。

触发事件：

insert
update
delete

删除触发器：

--删除触发器
DROPTRIGGERnewproduct;

INSERT触发器：

insert触发器在insert语句执行之前或者之后执行，需要注意以下几点：

在insert触发器代码内。可以引用一个名为NEW的虚拟表，访问被插入的行；
在beforeinsert触发器中，NEW中的值也可以被更新（允许更改被插入的值）
对于AUTO_INCREMENT列，NEW在insert执行之前包含0，在insert执行之后包含新的自动生成值

例子：插入一个新的订单时，生成一个新的订单号保存到order_num

CREATETRIGGERneworderAFTERINSERTONordersforEACHROW
SELECTNEW.order_numinto@ee;

insertINTOorders(order_date,cust_id)VALUES(NOW(),10001);
SELECT@eeasnum;

dropTRIGGERneworder;

解释：

创建一个neworder的触发器，在插入之后执行，且对每个插入行执行，在insert中有一个与orders表一摸一样的虚表，用NEW表示；

SELECTNEW.order_numinto@a;

在虚表中找到我们插入的数据的编号，将标号保存在a变量中；

检测：

insertINTOorders(order_date,cust_id)VALUES(NOW(),10001);
SELECT@eeasnum;

插入数据，输出插入数据的编号

删除：

dropTRIGGERneworder;

删除触发器。

例二：

在COURSE表上创建触发器，检查插入时是否出现课程名相同的记录，若有则不操作。

CREATETRIGGERtrg_course_in
BEFOREINSERTONcourse
FOREACHROW
BEGIN
DECLAREmsgVARCHAR(100);
IFEXISTS(SELECT*FROMcoursewherecname=NEW.cname)THEN
SETmsg='不能输入相同名称的课程';
SIGNALSQLSTATE'HY000'SETmessage_text=msg;
ENDIF;

END

例三：向student表中插入信息时，检查ssex的值必须为男或女。

CREATETRIGGERtrg_ssexAFTERINSERTonstudentFOREACHROW
BEGIN
DECLAREmsgVARCHAR(100);
IF(NEW.ssexnotin('男','女'))THEN
SETmsg='性别必须为男或女';
SIGNALSQLSTATE'HY000'SETmessage_text=msg;
ENDIF
END

UPDATE触发器：

在update触发器的代码中，可以引用一个名为OLD的虚拟表访问以前的值，即：update未执行前的值，还可以引用一个名为NEW的虚拟表访问新更新的值；
在beforeupdate触发器中，NEW中的值可能也被更新（允许修改将要用于update语句中的值）；
OLD中的值全部只读，不能更新。

例一：保证州名缩写为大写

CREATETRIGGERUPDATEevendorBEFOREUPDATEonvendors
FOREACHROWSETnew.vend_state=UPPER(new.vend_state);

UPDATEvendorsSETvend_state='hw'wherevend_id='1001';
DROPTRIGGERUPDATEevendor;

注：upper:将文本转换为大写：

例二：不允许修改student表中的学号sno，如果修改该列则显示错误信息并取消操作。

CREATETRIGGERtrg_student_updateSnoBEFOREUPDATE
FOREACHROW
BEGIN
DECLAREmsgVARCHAR(100);
IFNEW.sno<>OLD.snoTHEN
SETmsg='不允许修改sno';
SIGNALSQLSTATE'HY000'SETmessage_text=msg;
ENDIF;
END

DELETE触发器：

在DELETE触发器在delete语句执行之前或之后执行：

在delete触发器代码内，可以引用OLD的虚拟表，访问被删除的行；
OLD中的值全部都是只读，不能更新

例子：

使用old保存将要被删除的行到一个存档表中

首先先创建一个与orders相似的表：

CREATETABLEarchive_ordersLIKEorders;

--创建一个删除的触发器
CREATETRIGGERdeleteorderBEFOREDELETEonorders
forEACHROWBEGIN
INSERTINTOarchive_orders(order_num,order_date,cust_id)VALUES(old.order_num,old.order_date,old.cust_id);
END

解释:

在删除order表中行中信息时，将删除的信息保存到archive_orders中；

删除原表中一行：

DELETEFROMordersWHEREorder_num='20014';

查看效果：

SELECT*FROMarchive_orders;

结束：

注：如果遇到触发器报错“Notallowedtoreturnaresultsetfromatrigger”

原因：因为从MySQL5以后不支持触发器返回结果集
解决方法：在后面语句后面添加into@变量名
取数据：select@变量名

详细解释：https://www.programmersought.com/article/3237975256/

创建用户变量：https://www.nhooo.com/article/201843.htm

到此这篇关于MySQL触发器的使用场景及方法的文章就介绍到这了,更多相关MySQL触发器使用内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！