最新消息

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

乐鱼网教程 2021-04-07 21:18

触发器:


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


触发器可以使用的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@变量名


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

创建用户变量:http://www.jb51.net/article/201843.htm

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

您可能感兴趣的文章:详解MySQL数据库之触发器浅谈MySql视图、触发器以及存储过程mysql触发器创建与使用方法示例MySQL触发器基本用法详解【创建、查看、删除等】mysql触发器之触发器的增删改查操作示例mysql触发器之创建多个触发器操作实例分析MySQL触发器定义与用法简单实例MySQL触发器概念、原理与用法详解MySQL触发器的使用及需要注意的地方

退出