林炳文Evankaka原创作品。转载请注明出处

      触发器(TRIGGER)是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时,就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。在本文中将讲解的内容包括:
触发器的含义和作用
如何创建触发器
如何查看触发器
如何删除触发器

       触发器是由INSERT、UPDATE和DELETE等事件来触发某种特定操作。满足触发器的触发条件时,数据库系统就会执行触发器中定义的程序语句。这样做可以保证某些操作之间的一致性。例如,当学生表中增加了一个学生的信息时,学生的总数就必须同时改变。可以在这里创建一个触发器,每次增加一个学生的记录,就执行一次计算学生总数的操作。这样就可以保证每次增加学生的记录后,学生总数是与记录数是一致的。触发器触发的执行语句可能只有一个,也可能有多个。本节将详细讲解创建触发器的方法。

一、语法

创建只有一个执行语句的触发器

mysql中,创建只有一个执行语句的触发器的基本形式如下:

[sql]  view plain  copy
 
  1. CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件  
  2. ON 表名 FOR EACH ROW 执行语句  

创建有多个执行语句的触发器

 

MySQL中,触发器触发的执行语句可能有多个。创建有多个执行语句的触发器的基本形式如下:

[sql]  view plain  copy
 
  1. CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件  
  2. ON 表名 FOR EACH ROW  
  3. BEGIN  
  4. 执行语句列表  
  5. END  
下面来具体说说语法

 

1、命名规则

 

[sql]  view plain  copy
 
  1. CREATE TRIGGER <触发器名称> <--  
  2. { BEFORE | AFTER }  
  3. { INSERT | UPDATE | DELETE }  
  4. ON <表名称>  
  5. FOR EACH ROW  
  6. BEGIN  
  7. <触发器SQL语句>  
  8. END  

 

触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
2、触发时间: BEFORE | AFTER 
触发器有执行的时间设置:INSERT | UPDATE | DELETE 
3、触发事件前后
同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
4.、表
触发器是属于某一个表的:当在这个表上执行插入、
更新或删除操作的时候就导致触发器的激活.
我们不能给同一张表的同一个事件安排两个触发器。
5.、( 步长)触发间隔
触发器的执行间隔:FOR EACH ROW子句通知触发器
每隔一行执行一次动作,而不是对整个表执行一次。
6、 语句
触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句,
包括复合语句,但是这里的语句受的限制和函数的一样。
Privileges权限
你必须拥有相当大的权限才能创建触发器(CREATE TRIGGER)。如果你已经是Root用户,那么就足够了。这跟SQL的标准有所不同。


二、关于旧的和新创建的列的标识      在触发器的SQL语句中,你可以关联表中的任意列。但你不能仅使用列的名称去标识,那会使系统混淆,因为那里可能会有列的新名(这可能正是你要修改的,你的动作可能正是要修改列名),还有列的旧名存在。因此你必须用这样的语法来标识: "NEW . column_name"或者"OLD . column_name".这样在技术上处理(NEW | OLD . column_name)新和旧的列名属于创建了过渡变量("transition variables")。 

      对于INSERT语句,只有NEW是合法的;对于DELETE语句,只有OLD才合法;而UPDATE语句可以在和NEW以及OLD同时使用。

 

三、使用范例

先创建两个表、一个订单表,一个订单时间表

 

[sql]  view plain  copy
 
  1. CREATE TABLE T_ORDER(  
  2. ORDER_NUM INT PRIMARY KEY,  
  3. ORDER_NAME CHAR(10) NOT NULL  
  4. );  
  5. CREATE TABLE T_ORDER_TIME(  
  6. ORDER_NUM INT PRIMARY KEY,  
  7. ORDER_TIME TIMESTAMP NOT NULL,  
  8. )  

 

1、创建插入时的触发器

 

[sql]  view plain  copy
 
  1. CREATE TRIGGER TRI_INSERT  
  2. AFTER INSERT ON T_ORDER FOR EACH ROW  
  3. BEGIN  
  4. INSERT INTO learning.t_order_time(ORDER_NUM,ORDER_TIME) VALUES(NEW.ORDER_NUM,NOW());  
  5. END  
然后订单表中插入一条数据
[sql]  view plain  copy
 
  1. INSERT INTO T_ORDER(ORDER_NUM,ORDER_NAME) VALUES(1,'电脑');  
  2. SELECT * FROM t_order;  
  3. SELECT * FROM t_order_time;  
这是订单表中插入的数据:

 

MySql触发器使用讲解_MySQL

这是订单时间表中自动插入的数据:

MySql触发器使用讲解_MySQL_02

2、更新时插入数据

 

[sql]  view plain  copy
 
  1. //更新创建触发器  
  2. CREATE TRIGGER TRI_UPDATA  
  3. BEFORE UPDATE ON T_ORDER FOR EACH ROW  
  4. BEGIN  
  5. UPDATE  learning.t_order_time SET ORDER_TIME=NOW() WHERE OLD.ORDER_NUM=ORDER_NUM;  
  6. END  
  7.   
  8. UPDATE T_ORDER  SET ORDER_NAME='饼干饼' WHERE ORDER_NUM=1;  
  9. SELECT * FROM t_order;  
  10. SELECT * FROM t_order_time;  

 

MySql触发器使用讲解_MySQL_03


可以看到,时间已经更新

3、创建删除触发器

 

[java]  view plain  copy
 
  1. //创建删除触发器  
  2. CREATE TRIGGER TRI_DELETE  
  3. AFTER DELETE ON T_ORDER FOR EACH ROW  
  4. BEGIN  
  5. DELETE FROM  learning.t_order_time  WHERE OLD.ORDER_NUM=ORDER_NUM;  
  6. END  
  7.   
  8. DELETE FROM  learning.t_order  WHERE ORDER_NUM=1;  
  9. SELECT * FROM t_order;  
  10. SELECT * FROM t_order_time;  


 

可以看到,数据库中都没有数据了。
MySql触发器使用讲解_MySQL_04


4、查看触发器

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法等信息。查看触发器的方法包括SHOW TRIGGERS语句和查询information_schema数据库下的triggers表等。本节将详细讲解查看触发器的方法。

MySQL中,可以执行SHOW TRIGGERS语句来查看触发器的基本信息。其基本形式如下:

SHOW TRIGGERS ;

MySql触发器使用讲解_MySQL_05

 

MySQL中,所有触发器的定义都存在information_schema数据库下的triggers表中。查询triggers表,可以查看到数据库中所有触发器的详细信息。查询的语句如下:

SELECT *  FROM  information_schema.triggers;

MySql触发器使用讲解_MySQL_06

5、删除触发器

 

[sql]  view plain  copy
 
  1. DROP TRIGGER 触发器名  

 

6、触发器 与存储过程
触发程序不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL
(允许存储程序通过参数将数据返回触发程序)。 
而存储过程 可以接受参数,将结果范围给应用程序