六、触发器的操作

---------------------------------------------------------       小路原创           ------------------------------------------------

触发器简介：

触发器TRIGGER像高级编程语言中的函数，有声明和执行等。但是触发器的执行是由事件（delete、update和insert）来触发激活从而执行的。

触发器主要是用在当表发生更改时，自动进行一些处理，保证数据的完整性约束和业务规则等。

MySQL从5.5之后才开始支持触发器。

一、创建触发器

1、创建只有一条执行语句的触发器

create trigger trigger_name

before|after trigger_event

on table_name for each row trigger_stmt

其中before|after指定触发器在触发事件trigger_event之前执行还是之后执行；for each row表示任何一条记录上的操作满足触发器事件都会激活触发器；

trigger_stmt指定触发器执行的语句。

example:

create trigger tri_insertbuildings

before insert 

on buildings for each row 

select '程序正在往school.buildings表中插入数据！';

--下面的语句执行后将看到先输出“程序正在往school.buildings表中插入数据！”信息，然后才显示buildings表中的所有数据，说明触发器是在insert语句之前执行的。

insert into buildings

values(4,'4号教学楼','医学院');

select * from buildings;

2、创建有多条执行语句的触发器

create trigger trigger_name

before|after trigger_event

on table_name for each row 

begin 

trigger_stmt1;

……

trigger_stmtn;

end;

example:

drop trigger if exists tri_delbuildings;

create trigger tri_delbuildings

before delete

on buildings for each row 

begin 

insert into students

values('马小三',20,23);

insert into students

values('李奇葩',30,20);

end;

--成功执行！！！

delete from buildings where Id = 3;

select * from buildings;

select * from students;

--成功原因：因为触发触发器的事件是对buildings表进行更新的事件，而触发器中执行的是对students表的更新，是对两个不同的表的更新。

注意一下语句不能成功执行：

example:

drop trigger if exists tri_delbuildings;

create trigger tri_delbuildings

before delete

on buildings for each row 

begin 

insert into buildings

values(20,'20号教学楼','文学院');

insert into buildings

values(30,'30号教学楼','石油学院');

end;

--执行失败！！！

delete from buildings where Id = 3;

select * from buildings;

--报错信息： Can't update table 'buildings' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

--原因：在触发器中执行语句中引用了触发该触发器的宿主表，MySQL中不允许在更新一个表时触发相应的触发器，相应的触发器中的执行语句又是对同一个表执行更新，这将形成死循环。

--when you insert a record mysql is doing some lock stuff. you can't insert/update/delete rows of the same table where you insert.. because then the trigger would called again and again.. ending up in a recursion

--参考： http://forums.mysql.com/read.php?99,122354,122505#msg-122505

二、查看触发器

1、查看所有触发器

show triggers;

2、通过查看系统表triggers查看触发器

--系统数据库infomation_schema中的表triggers存储着所有触发器信息。

use information_schema;

select * from triggers;

或者

select * from triggers where trigger_name = 要查看的触发器名;

example:

use information_schema;

select * from triggers where trigger_name = 'tri_delbuildings';

三、删除触发器

use db_name;

drop trigger trigger_name;

example:

use school;

drop trigger tri_delbuildings;

show triggers;

---------------------------------------------------------       小路原创           ------------------------------------------------