mysql触发器
2018-05-11 本文已影响14人
StrongZhao
触发器介绍:
触发器是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等
使用触发器的优点:
- 可以来检查或预防坏的数据进入数据库。
- 可以改变或取消INSERT、UPDATE、以及DELETE语句。
- 可以在一个会话中监视数据改变的动作。
使用触发器的缺点:
- MySQL触发器始终时基于表中的一条记录触发,而不是一组SQL语句。因此,如果需要变动整个数据集而数据集数据量又较大时,触发器效果会非常低。
- 每一个表的一个事件只能定义一个触发器,而且表必须引用永久性表,不能将触发程序与TEMPORARY表或视图关联起来。
- 由于MySQL触发器基于行触发的特性,因此对于批量操作并不适合使用触发器
使用触发器实现的业务逻辑在出现问题时很难进行定位,特别是设计到多个触发器的情况 - 协同开发时,写业务层代码如果不清楚数据库触发器的细节,容易搞不清到底触发了那些触发器
- 大量使用触发器会导致代码结构容易被打乱,阅读源码困难
创建触发器:
- 命令模式如下:
create
[definer = { user | CURRENT_USER }]
trigger trigger_name
{before | after } {insert | update | delete}
ON table_name FOR EACH ROW
[{follows | precedes}] other_trigger_name
trigger_body
说明:
definer:指定用户名。
trigger_name: 触发器名称且名称最多不超过64个字符
before|after. 可以设置为事件发生前或后触发
insert:将新行插入表时激活触发程序,例如,通过insert、load data和replace语句。
update:更改某一行时激活触发程序
delete:从表中删除某一行时激活触发程序,例如,通过delete和replace语句。
table_name:触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活。我们不能给同一张表的同一个事件安排两个触发器,而且必须引用永久性表,不能将触发程序与TEMPORARY表或视图关联起来。
FOR EACH ROW 触发间隔通知触发器每隔一行执行一次动作,而不是对整个表执行一次。
关于旧的和新创建的列的标识
在触发器的SQL语句中,你可以关联表中的任意列。但你不能仅使用列的名称去标识,那会使系统混淆,因为那里可能会有列的新名(这可能正是你要修改的,你的动作可能正是要修改列名),还有列的旧名存在。因此你必须用这样的语法来标识: "NEW . column_name"或者"OLD . column_name".这样在技术上处理(NEW | OLD . column_name)新和旧的列名属于创建了过渡变量("transition variables")。
对于insert语句,只有NEW是合法的;对于delete语句,只有OLD才合法;而update语句可以在和NEW以及OLD同时使用。
ex:
--插入用户数据到user表中后,往user_temp表中同步一份用户数据
create definer= CURRENT_USER trigger insert_sync_user_data
after insert on user
for each row
begin
set @username = NEW.name;
insert into user_temp(name) value(@username);
end;
查看触发器:
- 命令模式如下:
SHOW TRIGGERS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
ex:
--创建一个test触发器
create definer = CURRENT_USER trigger test after insert on user for each row
set @name = NEW.name;
--查看全部的触发器
show triggers;
--查看以tes开头的触发器
show trigger like 'tes%'\G;
--查看test触发器
show create trigger test;
删除触发器:
- 命令模式如下:
drop trigger [if exists] [schema_name.]trigger_name
ex:
--删除insert_sync_user_data触发器
drop trigger if exists insert_sync_user_data;
--删除test触发器
drop trigger if exists test;