--------------------------------------------------------- 小路原创 ------------------------------------------------
触发器简介:
触发器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
二、查看触发器
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;
--------------------------------------------------------- 小路原创 ------------------------------------------------