首页 > 代码库 > mysql trigger 触发器
mysql trigger 触发器
创建触发器：
CREATE
[DEFINER = {user|CURRENT_USER}]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
语法中：
trigger_name：触发器的名称，不能与已经存在的触发器重复；
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；
trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；
tbl_name：该触发器作用在tbl_name上；
实例：
创建简单确发器
<1> 准备学生表和学生数目统计表
mysql> CREATE TABLE student_info (
-> stu_no INT(11) NOT NULL AUTO_INCREMENT,
-> stu_name VARCHAR(255) DEFAULT NULL,
-> PRIMARY KEY (stu_no)
-> );
mysql> CREATE TABLE student_count(
-> student_count INT(11) DEFAULT 0
-> );
插入一条数据:
mysql> INSERT INTO student_count VALUES(0);
<2> 创建简单触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
http://blog.csdn.net/goskalrie/article/details/53020631
mysql> CREATE TRIGGER trigger_student_count_insert
-> AFTER INSERT
-> ON student_count FOR EACH ROW
-> UPDATE student_count SET student_count=student_count+1;
mysql> CREATE TRIGGER trigger_student_count_insert
-> AFTER INSERT
-> ON student_info FOR EACH ROW
-> UPDATE student_count SET student_count=student_count+1;
mysql> CREATE TRIGGER trigger_student_count_delete
-> AFTER DELETE
-> ON student_info FOR EACH ROW
-> UPDATE student_count SET student_count=student_count-1;
<3> INSERT、DELETE数据，查看触发器是否正常工作
mysql> INSERT INTO student_info VALUES(NULL,‘xiaoc‘),(NULL,‘xiaoz‘),(NULL,‘xionan‘);
mysql> select * from student_info;
+--------+----------+
| stu_no | stu_name |
+--------+----------+
| 1 | xiaoc |
| 2 | xiaoz |
| 3 | xionan |
+--------+----------+
3 rows in set (0.00 sec)
mysql> desc student_info;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| stu_no | int(11) | NO | PRI | NULL | auto_increment |
| stu_name | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
删除确发器，修改确发器:
触发器会随着表的删除被删除！
查看触发器:
show triggers;
正解版：
mysql> CREATE TABLE student_info (
->
-> stu_no INT(11) NOT NULL AUTO_INCREMENT,
->
-> stu_name VARCHAR(255) DEFAULT NULL,
->
-> PRIMARY KEY (stu_no)
->
-> );
Query OK, 0 rows affected (0.22 sec)
mysql> CREATE TABLE student_count (
->
-> student_count INT(11) DEFAULT 0
->
-> );
Query OK, 0 rows affected (0.24 sec)
mysql> INSERT INTO student_count VALUES(0);
Query OK, 1 row affected (0.14 sec)
mysql> CREATE TRIGGER trigger_student_count_insert
->
-> AFTER INSERT
->
-> ON student_info FOR EACH ROW
->
-> UPDATE student_count SET student_count=student_count+1;
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TRIGGER trigger_student_count_delete
->
-> AFTER DELETE
->
-> ON student_info FOR EACH ROW
->
-> UPDATE student_count SET student_count=student_count-1;
Query OK, 0 rows affected (0.14 sec)
mysql> INSERT INTO student_info VALUES(NULL,‘xiaoc‘),(NULL,‘xiaoz‘),(NULL,‘xionan‘);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student_info;
+--------+----------+
| stu_no | stu_name |
+--------+----------+
| 1 | xiaoc |
| 2 | xiaoz |
| 3 | xionan |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select * from student_count;
+---------------+
| student_count |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> delete from student_info where stu_name in (‘xionan‘,‘xiaoc‘);
Query OK, 2 rows affected (0.06 sec)
mysql> select * from student_count;
+---------------+
| student_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
mysql> insert into student_info values(null,‘xiaol‘);
Query OK, 1 row affected (0.06 sec)
mysql> select * from student_info;
+--------+----------+
| stu_no | stu_name |
+--------+----------+
| 2 | xiaoz |
| 4 | xiaol |
+--------+----------+
2 rows in set (0.00 sec)
mysql> select * from student_count;
+---------------+
| student_count |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
可以看到无论是INSERT还是DELETE学生，学生数目都会跟变化的。
创建包含多条执行语句的触发器:
在trigger_body中可以执行多条SQL语句，此时的trigger_body需要使用BEGIN和END做为开始和结束的标志：
CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
BEGIN
trigger_statement
END;
示例2，创建包含多条执行语句的触发器：
(删除触发器)
mysql> show triggers\G;
*************************** 1. row ***************************
Trigger: trigger_student_count_insert
Event: INSERT
Table: student_info
Statement: UPDATE student_count SET student_count=student_count+1
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Trigger: trigger_student_count_delete
Event: DELETE
Table: student_info
Statement: UPDATE student_count SET student_count=student_count-1
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> DROP TRIGGER trigger_student_count_insert;
Query OK, 0 rows affected (0.10 sec)
mysql> DROP TRIGGER trigger_student_count_delete;
Query OK, 0 rows affected (0.00 sec)
mysql> show triggers;
Empty set (0.00 sec)
依然沿用上面的例子中的表，对student_count表做如下变更：增加student_class字段表示具体年级的学生数，其中0表示全年级，1代表1年级……；同样学生表中也增加该字段。清空两个表中的所有数据。
1:对student_count表增加student_class字段：
mysql> show create table student_count;
+---------------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-------------------------------------------------------------------------------------------------------------+
| student_count | CREATE TABLE `student_count` (
`student_count` int(11) DEFAULT ‘0‘
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table student_count add student_class int(11) default ‘0‘;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student_count;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| student_count | CREATE TABLE `student_count` (
`student_count` int(11) DEFAULT ‘0‘,
`student_class` int(11) DEFAULT ‘0‘
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
<1> 删除上例中的两个触发器，初始化student_count表中数据，插入三条数据(0,0),(1,0),(2,0)表示全年级、一年级、二年级的初始人数都是0；
mysql> update student_count set student_count=0;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student_count;
+---------------+---------------+
| student_count | student_class |
+---------------+---------------+
| 0 | 0 |
+---------------+---------------+
1 row in set (0.00 sec)
mysql> insert student_count values(1,0),(2,0);
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student_count;
+---------------+---------------+
| student_count | student_class |
+---------------+---------------+
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
+---------------+---------------+
3 rows in set (0.00 sec)
<2> 创建触发器，在INSERT时首先增加学生总人数，然后判断新增的学生是几年级的，再增加对应年级的学生总数：
mysql> select * from student_info;
+--------+----------+
| stu_no | stu_name |
+--------+----------+
| 2 | xiaoz |
| 4 | xiaol |
+--------+----------+
2 rows in set (0.00 sec)
mysql> delete from student_info where stu_name=‘xiaoz‘;
Query OK, 1 row affected (0.03 sec)
mysql> delete from student_info where stu_name=‘xiaol‘;
Query OK, 1 row affected (0.06 sec)
mysql> select * from student_info;
Empty set (0.00 sec)
上面是先清理一下数据：
字段的增加有误，顺序有误:
mysql> alter table student_count drop student_count;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_count;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| student_class | int(11) | YES | | 0 | |
+---------------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> alter table student_count add student_count int(11) default ‘0‘;
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student_count;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| student_count | CREATE TABLE `student_count` (
`student_class` int(11) DEFAULT ‘0‘,
`student_count` int(11) DEFAULT ‘0‘
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from student_count where student_class=0;
Query OK, 3 rows affected (0.06 sec)
重新插入数据：
mysql> insert student_count values(0,0),(1,0),(2,0);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student_count;
+---------------+---------------+
| student_class | student_count |
+---------------+---------------+
| 0 | 0 |
| 1 | 0 |
| 2 | 0 |
+---------------+---------------+
3 rows in set (0.00 sec)
http://blog.csdn.net/goskalrie/article/details/53020631
对student_count表做如下变更：增加student_class字段表示具体年级的学生数，其中0表示全年级，
1代表1年级……；同样学生表中也增加该字段。清空两个表中的所有数据。
mysql> alter table student_info add column student_class int//
Query OK, 0 rows affected (1.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student_info//
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| stu_no | int(11) | NO | PRI | NULL | auto_increment |
| stu_name | varchar(255) | YES | | NULL | |
| student_class | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
创建触发器，在INSERT时首先增加学生总人数，然后判断增加学生总人数，然后判断新增的学生几年级的，
再增加对应年级的学生总数。
mysql> delimiter $$
mysql> create trigger trigger_student_count_insert
-> after insert
-> on student_info for each row
-> begin
-> update student_count set student_count=student_count+1 where student_class=0;
-> update student_count set student_count=student_count+1 where student_class=new.student_class;
-> end
-> $$
Query OK, 0 rows affected (0.09 sec)
mysql> delimiter;
创建触发器，在DELETE时首先减少学生总人数，然后判断删除的学生是几年级的，再减少对应年级的学生总数；
mysql> DELIMITER $$
mysql>
mysql> CREATE TRIGGER trigger_student_count_delete
->
-> AFTER DELETE
->
-> ON student_info FOR EACH ROW
->
-> BEGIN
->
-> UPDATE student_count SET student_count=student_count-1 WHERE student_class=0;
->
-> UPDATE student_count SET student_count=student_count-1 WHERE student_class= OLD.student_class;
->
-> END
->
-> $$
Query OK, 0 rows affected (0.15 sec)
mysql>
mysql> DELIMITER ;
mysql> desc student_info//
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| stu_no | int(11) | NO | PRI | NULL | auto_increment |
| stu_name | varchar(255) | YES | | NULL | |
| student_class | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
向学生表中分别插入多条不同年级的学生信息，查看触发器是否起做用：
mysql> INSERT INTO student_info VALUES(NULL,‘AAA‘,1),(NULL,‘BBB‘,1),(NULL,‘CCC‘,2),(NULL,‘DDD‘,2),(NULL,‘ABB‘,1),(NULL,‘ACC‘,1);
Query OK, 6 rows affected (0.12 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from student_info;
+--------+----------+---------------+
| stu_no | stu_name | student_class |
+--------+----------+---------------+
| 1 | AAA | 1 |
| 2 | BBB | 1 |
| 3 | CCC | 2 |
| 4 | DDD | 2 |
| 5 | ABB | 1 |
| 6 | ACC | 1 |
+--------+----------+---------------+
6 rows in set (0.00 sec)
mysql> select * from student_count;
+---------------+---------------+
| student_class | student_count |
+---------------+---------------+
| 1 | 4 |
| 2 | 2 |
| 0 | 6 |
+---------------+---------------+
3 rows in set (0.00 sec)
可以看到，总共插入6条数据，学生总数是6，1年级4个，2年级2个，trigger正确执行。
从学生表中分别岀除多条不同年级的学生信息，查看触发器是否起作用：
mysql> delete from student_info where stu_name like ‘A%‘;
Query OK, 3 rows affected (0.15 sec)
mysql> SELECT * FROM student_info;
+--------+----------+---------------+
| stu_no | stu_name | student_class |
+--------+----------+---------------+
| 2 | BBB | 1 |
| 3 | CCC | 2 |
| 4 | DDD | 2 |
+--------+----------+---------------+
3 rows in set (0.00 sec)
mysql> select * from student_count;
+---------------+---------------+
| student_class | student_count |
+---------------+---------------+
| 1 | 1 |
| 2 | 2 |
| 0 | 3 |
+---------------+---------------+
3 rows in set (0.00 sec)
从学生表中将姓名以A开头的学生信息删除，学生信息删除的同时，数量表也跟随变化。
在上面的示例中，使用了三个新的关键字：DELIMITER、NEW、OLD，这三个关键字在官网上“触发器语法”一节中都有介绍
mysql trigger 触发器
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。