MySQL 触发器的使用及需要注意的地方
关于触发器
现实开发中我们经常会遇到这种情况,比如添加、删除和修改信息的时候需要记录日志,我们就要在完成常规的数据库逻辑操作之后再去写入日志表,这样变成了两步操作,更复杂了。
又比如删除一个人员信息的时候,需要将他的购物记录、收货地址、收藏夹等都删了,这个连续的操作容易出错,一致性和完整性不好保证。这时候就要使用触发器,既可以免去一堆的业务逻辑代码,又能更好的保证数据完整性。
触发器(trigger)是一种与表有关的数据库对象:在满足定义条件时触发某种操作,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端保证数据的完整性。
它是一种与表事件相关的特殊的存储过程,当对一个表进行操作(insert,delete,update)时就会激活执行。
触发器的使用
创建触发器
创建触发器的语法如下:
CREATETRIGGERtrigger_nametrigger_timetrigger_eventONt_nameFOREACHROWtrigger_stmt
说明:
trigger_name:触发器名称
tirgger_time:触发执行时间点,数据操作前(BEFORE)或者数据操作后(AFTER)
trigger_event:触发事件,增(INSERT)、删(DELETE)、改(UPDATE)
t_name:指的是在t_name这张表上建立触发器
trigger_stmt:触发器的程序体,可以是一条SQL语句或者是用BEGIN和END包含的多条语句
触发器只能创建在永久表(Permanent)上,不能创建在临时表(Temporary)上
FOREACHROW固定表达式,表示任何一条记录上的操作满足触发事件都会触发该触发器
tirgger_time有两种类型,trigger_event有三种类型,所以他们一共有6种组合:BEFOREINSERT,BEFOREDELETE,BEFOREUPDATE,AFTERINSERT,AFTERDELETE,AFTERUPDATE
示例(先创建一个日志表,用于触发器触发时候填入值):
/*先创建一个日志表,用于触发器触发时候填入值*/ mysql>DROPTABLEIFEXISTS`TriggerLog`; QueryOK,0rowsaffected mysql>CREATETABLE`TriggerLog` ( `id`INTauto_incrementPRIMARYKEY, `trigger_time`VARCHAR(30), `trigger_event`VARCHAR(30), `memo`VARCHAR(200) ); QueryOK,0rowsaffected
Insert类型触发器:
mysql> /*这边声明SQL脚本的结束符为//*/ DELIMITER// DROPTRIGGERIFEXISTStrig_after_insert; CREATETRIGGERtrig_after_insertAFTERINSERTONstudentsFOREACHROW BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','insert',concat('newstudentinfo,id:',cast(new.studentidaschar))); END// QueryOK,0rowsaffected mysql> /*重置SQL脚本的结束符为;*/ DELIMITER; QueryOK,0rowsaffected mysql> /*插入一条数据*/ insertintostudents(studentname,score,classid)values('trigger1',100,0); QueryOK,1rowaffected mysql> /*查询日志表看是否有触发写入*/ select*from`TriggerLog`; +----+--------------+---------------+------------------------+ |id|trigger_time|trigger_event|memo| +----+--------------+---------------+------------------------+ |1|after|insert|newstudentinfo,id:21| +----+--------------+---------------+------------------------+ 1rowinset
Update类型触发器:
mysql> /*这边声明SQL脚本的结束符为//*/ DELIMITER// DROPTRIGGERIFEXISTStrig_after_update; CREATETRIGGERtrig_after_updateAFTERUPDATEONstudentsFOREACHROW BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','update',concat('updatestudentinfo,id:',cast(new.studentidaschar))); END// QueryOK,0rowsaffected mysql> /*重置SQL脚本的结束符为;*/ DELIMITER; QueryOK,0rowsaffected mysql> /*更新数据*/ updatestudentssetscore=99wherestudentname='trigger1'; QueryOK,1rowaffected Rowsmatched:1Changed:1Warnings:0 mysql> /*查询日志表看是否更新的时候有触发写入*/ select*from`TriggerLog`; +----+--------------+---------------+---------------------------+ |id|trigger_time|trigger_event|memo| +----+--------------+---------------+---------------------------+ |1|after|insert|newstudentinfo,id:21| |2|after|update|updatestudentinfo,id:21| +----+--------------+---------------+---------------------------+ 2rowsinset
Delete类型触发器:
mysql> /*这边声明SQL脚本的结束符为//*/ DELIMITER// DROPTRIGGERIFEXISTStrig_after_delete; CREATETRIGGERtrig_after_deleteAFTERDELETEONstudentsFOREACHROW BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','update',concat('deletestudentinfo,id:',cast(old.studentidaschar))); END// QueryOK,0rowsaffected mysql> /*重置SQL脚本的结束符为;*/ DELIMITER; QueryOK,0rowsaffected mysql> /*删除数据*/ deletefromstudentswherestudentid=21; QueryOK,1rowaffected mysql> /*查询日志看删除是否有触发写入*/ select*from`TriggerLog`; +----+--------------+---------------+---------------------------+ |id|trigger_time|trigger_event|memo| +----+--------------+---------------+---------------------------+ |1|after|insert|newstudentinfo,id:21| |2|after|update|updatestudentinfo,id:21| |3|after|update|deletestudentinfo,id:21| +----+--------------+---------------+---------------------------+ 3rowsinset
查看触发器
查看全部触发器
showtriggers;--语法
mysql>showtriggers; +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ |Trigger|Event|Table|Statement|Timing|Created|sql_mode|Definer|character_set_client|collation_connection|DatabaseCollation| +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ |trig_after_insert|INSERT|students|BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','insert',concat('newstudentinfo,id:',cast(new.studentidaschar))); END|AFTER|NULL|STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|root@localhost|utf8|utf8_general_ci|latin1_swedish_ci| |trig_after_update|UPDATE|students|BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','update',concat('updatestudentinfo,id:',cast(new.studentidaschar))); END|AFTER|NULL|STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|root@localhost|utf8|utf8_general_ci|latin1_swedish_ci| |trig_after_delete|DELETE|students|BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','update',concat('deletestudentinfo,id:',cast(old.studentidaschar))); END|AFTER|NULL|STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|root@localhost|utf8|utf8_general_ci|latin1_swedish_ci| +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3rowsinset
查看触发器的创建语句
showcreatetriggertrigger_name;--语法
mysql>showcreatetriggertrig_after_insert; +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ |Trigger|sql_mode|SQLOriginalStatement|character_set_client|collation_connection|DatabaseCollation| +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ |trig_after_insert|STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION|CREATEDEFINER=`root`@`localhost`TRIGGERtrig_after_insertAFTERINSERTONstudentsFOREACHROW BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','insert',concat('newstudentinfo,id:',cast(new.studentidaschar))); END|utf8|utf8_general_ci|latin1_swedish_ci| +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 1rowinset
删除触发器
droptriggertrigger_name;--语法
mysql>droptriggertrig_after_insert; QueryOK,0rowsaffected mysql>showcreatetriggertrig_after_insert; 1360-Triggerdoesnotexist
使用时的注意点
NEW/OLD的使用区别
触发器针对的是数据库中的每一行记录,每行数据在操作前后都会有一个对应的状态,触发器将没有操作之前的状态保存到old关键字中,将操作后的状态保存到new中
new.cname--新增行(或修改前的行)的某列数据 old.cname--删除行(或修改后的行)的某列数据
需要注意的是,old和new不是所有触发器都有
触发器类型 | new和old的使用 |
---|---|
INSERT型触发器 | 没有old,只有new,new表示将要(插入前)或者已经增加(插入后)的数据 |
UPDATE型触发器 | 既有old也有new,old表示更新之前的数据,new表示更新之后的数据 |
DELETE型触发器 | 没有new,只有old,old表示将要(删除前)或者已经被删除(删除后)的数据 |
其实我们上面已经用过new/old来创建触发器并获取数据,我们根据这个表格重新来修改下那个更新触发器(trig_after_update),输出修改前和修改后的内容比较:
mysql> /*这边声明SQL脚本的结束符为//*/ DELIMITER// DROPTRIGGERIFEXISTStrig_after_update; CREATETRIGGERtrig_after_updateAFTERUPDATEONstudentsFOREACHROW BEGIN insertinto`TriggerLog`(`trigger_time`,`trigger_event`,`memo`)values('after','update',concat('from:',old.studentname,',',old.score,'','to:',new.studentname,',',new.score)); END// QueryOK,0rowsaffected mysql> /*重置SQL脚本的结束符为;*/ DELIMITER; QueryOK,0rowsaffected mysql> /*更新成绩和名称*/ updatestudentssetscore=106,studentname='trigger2'wherestudentid=17; QueryOK,1rowaffected Rowsmatched:1Changed:1Warnings:0 mysql> /*根据old和new对比更新前后的值*/ select*from`TriggerLog`; +----+--------------+---------------+--------------------------------------+ |id|trigger_time|trigger_event|memo| +----+--------------+---------------+--------------------------------------+ |1|after|insert|newstudentinfo,id:21| |2|after|update|updatestudentinfo,id:21| |3|after|update|deletestudentinfo,id:21| |4|after|update|from:test2,101.00to:trigger2,106.00| +----+--------------+---------------+--------------------------------------+ 4rowsinset
无法触发对同表的修改
MySQL的触发器中不能对本表进行insert、update和delete操作,否则会报错
mysql> /*这边声明SQL脚本的结束符为//*/ DELIMITER// DROPTRIGGERIFEXISTStrig_after_insert; CREATETRIGGERtrig_after_insertAFTERINSERTONstudentsFOREACHROW BEGIN updatestudentssetscore=score+1wherestudentid=new.studentid; END// QueryOK,0rowsaffected mysql> /*重置SQL脚本的结束符为;*/ DELIMITER; QueryOK,0rowsaffected mysql> /*插入一条数据之后因为触发了对本表的修改,所以报错了*/ insertintostudents(studentname,score,classid)values('trigger2',101,0); 1442-Can'tupdatetable'students'instoredfunction/triggerbecauseitisalreadyusedbystatementwhichinvokedthisstoredfunction/trigger.
小结
1、触发器可以通过数据库中的关联表实现级联更改,即一张表数据的改变会影响其他表的数据,如数据修改,数据统计,数据复制等。
2、可以保证数据安全,并进行安全校验,限制用户具有操作数据库的某种权利。
3、可以对复杂逻辑的实现进行数据完整性检查和约束。
4、使用触发器的要应需而用,如果过分依赖触发器,会影响数据库的结构,增加数据库的执行和维护成本。
5、触发器有BEFORE触发器和AFTER触发器的却别,所以它的执行步骤是先执行BEFORE触发器,再执行业务脚本,最后执行AFTER触发器。需要注意的是某一个步骤错误后就不再执行了,如果是事务表,会被回滚,如果是非事务表,那么就无法回滚了,数据可能有不一致性。
触发器的两种限制
1、触发程序不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL语句,但是允许存储程序通过参数将数据返回触发程序,也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。
2、不能再触发器中使用以显示或隐式方式开始或结束事务的语句,如STARTTRANS-ACTION,COMMIT或ROLLBACK。
以上就是MySQL触发器的使用及需要注意的地方的详细内容,更多关于MySQL触发器的资料请关注毛票票其它相关文章!