MySQL触发器Triggers
========================================================
触发器简介
创建触发器
查看触发器
删除触发器
触发器案例
一、触发器简介
触发器(trigger)是一个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,
比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整
性约束和业务规则等。
例如,当学生表中增加了一个学生的信息时,学生的总数就应该同时改变。因此可以针对学生表创建一个触发
器,每次增加一个学生记录时,就执行一次学生总数的计算操作,从而保证学生总数与记录数的一致性。
二、创建Trigger
语法:
CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
触发器程序体;
END
<触发器名称> 最多64个字符,它和MySQL中其他对象的命名方式一样
{ BEFORE | AFTER } 触发器时机
{ INSERT | UPDATE | DELETE } 触发的事件(插入,修改,删除)
ON <表名称> 标识建立触发器的表名,即在哪张表上建立触发器
FOR EACH ROW 触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行 执行一次动作,而不是对整个表执行一次
<触发器程序体> 要触发的SQL语句:可用顺序,判断,循环等语句实现一般程序需要的逻辑功能
example1
- 创建表
mysql> create table student(
-> id int unsigned auto_increment primary key not null,
-> name varchar(50)
-> );
mysql> insert into student(name) values(‘jack’);
mysql> create table student_total(total int);
mysql> insert into student_total values(1);
-
创建触发器student_insert_trigger
mysql> delimiter m y s q l > c r e a t e t r i g g e r s t u d e n t i n s e r t t r i g g e r a f t e r i n s e r t 在 录 入 之 后 触 动 触 发 器 − > o n s t u d e n t f o r e a c h r o w 每 隔 一 行 执 行 一 次 − > B E G I N − > u p d a t e s t u d e n t t o t a l s e t t o t a l = t o t a l + 1 ; − > E N D mysql> create trigger student_insert_trigger after insert 在录入之后触动触发器 -> on student for each row 每隔一行执行一次 -> BEGIN -> update student_total set total=total+1; -> END mysql>createtriggerstudentinserttriggerafterinsert在录入之后触动触发器−>onstudentforeachrow每隔一行执行一次−>BEGIN−>updatestudenttotalsettotal=total+1;−>END
mysql> delimiter ; -
创建触发器student_delete_trigger
mysql> delimiter m y s q l > c r e a t e t r i g g e r s t u d e n t d e l e t e t r i g g e r a f t e r d e l e t e 在 删 除 之 后 触 动 触 发 器 − > o n s t u d e n t f o r e a c h r o w − > B E G I N − > u p d a t e s t u d e n t t o t a l s e t t o t a l = t o t a l − 1 ; − > E N D mysql> create trigger student_delete_trigger after delete 在删除之后触动触发器 -> on student for each row -> BEGIN -> update student_total set total=total-1; -> END mysql>createtriggerstudentdeletetriggerafterdelete在删除之后触动触发器−>onstudentforeachrow−>BEGIN−>updatestudenttotalsettotal=total−1;−>END
mysql> delimiter ;
三、查看触发器
-
通过SHOW TRIGGERS语句查看
SHOW TRIGGERS\G -
通过系统表triggers查看
USE information_schema
SELECT * FROM triggers\G
SELECT * FROM triggers WHERE TRIGGER_NAME=‘触发器名称’\G
四、删除触发器
- 通过DROP TRIGGERS语句删除
DROP TRIGGER 解发器名称
五、触发器案例
example2
创建表tab1
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
id int primary key auto_increment,
name varchar(50),
sex enum(‘m’,‘f’),
age int
);
创建表tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
id int primary key auto_increment,
name varchar(50),
salary double(10,2)
);
触发器tab1_after_delete_trigger
作用:tab1表删除记录后,自动将tab2表中对应记录删除
mysql> \d
d
e
l
i
m
i
t
e
r
m
y
s
q
l
>
c
r
e
a
t
e
t
r
i
g
g
e
r
t
a
b
1
a
f
t
e
r
d
e
l
e
t
e
t
r
i
g
g
e
r
−
>
a
f
t
e
r
d
e
l
e
t
e
o
n
t
a
b
1
−
>
f
o
r
e
a
c
h
r
o
w
−
>
B
E
G
I
N
−
>
d
e
l
e
t
e
f
r
o
m
t
a
b
2
w
h
e
r
e
n
a
m
e
=
o
l
d
.
n
a
m
e
;
−
>
E
N
D
delimiter mysql> create trigger tab1_after_delete_trigger -> after delete on tab1 -> for each row -> BEGIN -> delete from tab2 where name=old.name; -> END
delimitermysql>createtriggertab1afterdeletetrigger−>afterdeleteontab1−>foreachrow−>BEGIN−>deletefromtab2wherename=old.name;−>END
mysql> \d ;
触发器tab1_after_update_trigger
作用:当tab1更新后,自动更新tab2
mysql> \d
m
y
s
q
l
>
c
r
e
a
t
e
t
r
i
g
g
e
r
t
a
b
1
a
f
t
e
r
u
p
d
a
t
e
t
r
i
g
g
e
r
−
>
a
f
t
e
r
u
p
d
a
t
e
o
n
t
a
b
1
−
>
f
o
r
e
a
c
h
r
o
w
−
>
B
E
G
I
N
−
>
u
p
d
a
t
e
t
a
b
2
s
e
t
n
a
m
e
=
n
e
w
.
n
a
m
e
−
>
w
h
e
r
e
n
a
m
e
=
o
l
d
.
n
a
m
e
;
−
>
E
N
D
mysql> create trigger tab1_after_update_trigger -> after update on tab1 -> for each row -> BEGIN -> update tab2 set name=new.name -> where name=old.name; -> END
mysql>createtriggertab1afterupdatetrigger−>afterupdateontab1−>foreachrow−>BEGIN−>updatetab2setname=new.name−>wherename=old.name;−>END
Query OK, 0 rows affected (0.19 sec)
mysql> \d;
触发器tab1_after_insert_trigger
作用:当tab1增加记录后,自动增加到tab2
mysql> \d
m
y
s
q
l
>
c
r
e
a
t
e
t
r
i
g
g
e
r
t
a
b
1
a
f
t
e
r
i
n
s
e
r
t
t
r
i
g
g
e
r
−
>
a
f
t
e
r
i
n
s
e
r
t
o
n
t
a
b
1
−
>
f
o
r
e
a
c
h
r
o
w
−
>
B
E
G
I
N
−
>
i
n
s
e
r
t
i
n
t
o
t
a
b
2
(
n
a
m
e
,
s
a
l
a
r
y
)
v
a
l
u
e
s
(
n
e
w
.
n
a
m
e
,
5000
)
;
−
>
E
N
D
mysql> create trigger tab1_after_insert_trigger -> after insert on tab1 -> for each row -> BEGIN -> insert into tab2(name,salary) values(new.name,5000); -> END
mysql>createtriggertab1afterinserttrigger−>afterinsertontab1−>foreachrow−>BEGIN−>insertintotab2(name,salary)values(new.name,5000);−>END
Query OK, 0 rows affected (0.19 sec)
mysql> \d;
example3
mysql> create table t1(
-> id int primary key auto_increment,
-> name varchar(50),
-> salary float(10,2)
-> );
Query OK, 0 rows affected (0.63 sec)
mysql> create table t2(
-> id int primary key auto_increment,
-> total_num int, //员工总人数
-> total_salary float(10,2) //员工薪水总和
-> );
Query OK, 0 rows affected (0.64 sec)
mysql> \d
m
y
s
q
l
>
c
r
e
a
t
e
t
r
i
g
g
e
r
t
1
a
f
t
e
r
i
n
s
e
r
t
t
r
i
g
g
e
r
−
>
a
f
t
e
r
i
n
s
e
r
t
o
n
t
1
−
>
f
o
r
e
a
c
h
r
o
w
−
>
B
E
G
I
N
−
>
u
p
d
a
t
e
t
2
s
e
t
t
o
t
a
l
n
u
m
=
t
o
t
a
l
n
u
m
+
1
,
t
o
t
a
l
s
a
l
a
r
y
=
t
o
t
a
l
s
a
l
a
r
y
+
n
e
w
.
s
a
l
a
r
y
;
−
>
E
N
D
mysql> create trigger t1_after_insert_trigger -> after insert on t1 -> for each row -> BEGIN -> update t2 set total_num=total_num+1, total_salary=total_salary+new.salary; -> END
mysql>createtriggert1afterinserttrigger−>afterinsertont1−>foreachrow−>BEGIN−>updatet2settotalnum=totalnum+1,totalsalary=totalsalary+new.salary;−>END
Query OK, 0 rows affected (0.54 sec)
mysql> insert into t2 values(0,0);
Query OK, 1 row affected (0.10 sec)
mysql> select * from t2;
±----------±-------------+
| total_num | total_salary |
±----------±-------------+
| 0 | 0.00 |
±----------±-------------+
1 row in set (0.00 sec)