MYSQL--触发器详解

触发器

1.触发器简介

触发器（trigger）是一个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数的一致性。

1.1 创建触发器

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语句：可用顺序，判断，循环等语句实现一般程序需要的逻辑功能

示例一

创建触发器： 
create trigger t_xxx before|after insert|delete|update
on table_name for each row
beginselect * from exxx;xxx
end注意：在命令行如果定义了各种SQL编程中的数据库对象，因为这些对象中存在多条SQL，需要通过;间隔SQL，因此我们需要在命令行中修改SQL结束的提交符mysql> delimiter /  #切换结束提交符，（临时生效）# 创建触发器
mysql> use db_exercise/
Database changed
mysql> create table student(-> id int unsigned auto_increment primary key not null,-> name varchar(50)-> )/
Query OK, 0 rows affected (0.02 sec)mysql> insert into student(name) values('jack');/
Query OK, 1 row affected (0.01 sec)mysql> select * from student/
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  2 | jack |
+----+------+
2 rows in set (0.00 sec)mysql> create table student_total(total int)/
Query OK, 0 rows affected (0.01 sec)mysql> insert into student_total values(2)/
Query OK, 1 row affected (0.01 sec)mysql> select * from student_total/
+-------+
| total |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)mysql> create trigger trig_stu_total-> after insert -> on student for each row-> begin-> update student_total set total = total + 1;-> end/
Query OK, 0 rows affected (0.01 sec)mysql> insert into student(name) values('tom');-> /
Query OK, 1 row affected (0.01 sec)mysql> select * from student/
+----+------+
| id | name |
+----+------+
|  1 | jack |
|  2 | jack |
|  3 | tom  |
+----+------+
3 rows in set (0.00 sec)mysql> select * from student_total/
+-------+
| total |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

1.2查看触发器

1. 通过SHOW TRIGGERS语句查看
SHOW TRIGGERS\G
2. 通过系统表triggers查看
USE information_schema
SELECT * FROM triggers\G
SELECT * FROM triggers WHERE TRIGGER_NAME='触发器名称'\G

1.3删除触发器

删除触发器
通过drop trigger 触发器名称

示例2

# 创建表tab1
mysql> create table tab1(-> id int  primary key auto_increment,-> name varchar(50),-> sex enum('m','f'),-> age int);
Query OK, 0 rows affected (0.02 sec)
# 创建表tab2
mysql> create table tab2(-> id int  primary key auto_increment,-> name varchar(50),-> salary double(10,2));
Query OK, 0 rows affected, 1 warning (0.01 sec)# 触发器tab1_after_delete_trigger
# 作用：tab1表删除记录后，自动将tab2表中对应记录删除
mysql> \d /
mysql> create trigger tab1_after_delete_trigger-> after delete on tab1-> for each row-> begin-> delete from tab2 where name=old.name;-> end/
Query OK, 0 rows affected (0.00 sec)# 触发器tab1_after_update_trigger
# 作用：当tab1更新后，自动更新tab2
Database changed
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$$
Query OK, 0 rows affected (0.00 sec)# 触发器tab2_after_insert_trigger
# 作用：当tab1增加记录后，自动增加到tab2
mysql> create trigger tab2_after_update_trigger-> after insert on tab1-> for each row-> begin-> insert into tab2 values(null, name, 5000);-> end/
Query OK, 0 rows affected (0.01 sec)