Mysql学习总结（10）——MySql触发器使用讲解

我会带着你远行 2022-10-02 08:51 224阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

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

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

# 一、语法 #

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

MySQL中，创建只有一个执行语句的触发器的基本形式如下：

**\[sql\]**  [view plain][] [copy][view plain]

1.  **CREATE** **TRIGGER** 触发器名 BEFORE | **AFTER** 触发事件  
2.  **ON** 表名 **FOR** EACH ROW 执行语句

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

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

**\[sql\]**  [view plain][] [copy][view plain]

1.  **CREATE** **TRIGGER** 触发器名 BEFORE | **AFTER** 触发事件  
2.  **ON** 表名 **FOR** EACH ROW  
3.  **BEGIN**  
4.  执行语句列表  
5.  **END**

下面来具体说说语法

**1、命名规则**

**\[sql\]**  [view plain][] [copy][view plain]

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][view plain]

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][view plain]

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][view plain]

1.  **INSERT** **INTO** T\_ORDER(ORDER\_NUM,ORDER\_NAME) **VALUES**(1,'电脑');  
2.  **SELECT** \* **FROM** t\_order;  
3.  **SELECT** \* **FROM** t\_order\_time;

这是订单表中插入的数据：

![04175355_wdzY.jpg][]

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

![04175355_G38l.jpg][]

2、更新时插入数据

**\[sql\]**  [view plain][] [copy][view plain]

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;

![04175355_3nVi.jpg][]

可以看到，时间已经更新

3、创建删除触发器

**\[java\]**  [view plain][] [copy][view plain]

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;

可以看到，数据库中都没有数据了。

![04175355_Pgh6.jpg][]

4、查看触发器

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

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

SHOW TRIGGERS ;

![04175355_lrrk.jpg][]

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

SELECT \*  FROM  information\_schema.triggers;

![04175356_gn4o.jpg][]

5、删除触发器

**\[sql\]**  [view plain][] [copy][view plain]

1.  **DROP** **TRIGGER** 触发器名

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

转载于:https://my.oschina.net/zhanghaiyang/blog/597008

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/08fb219a99484daaa63ca9b5e242b260.png
[view plain]: http://blog.csdn.net/evankaka/article/details/47377085#
[04175355_wdzY.jpg]: /images/20220113/d4f53ff88e124d3286647f38c275647c.png
[04175355_G38l.jpg]: /images/20220113/9f781f8e5f3f4b22b0bf98365157ab7a.png
[04175355_3nVi.jpg]: /images/20220113/0ce9634569a243a79a6742423c5d7cd7.png
[04175355_Pgh6.jpg]: /images/20220113/c6ff9f2f9a484ebab2416391045d3e0b.png
[04175355_lrrk.jpg]: /images/20220113/3621962d93604eab8dad4997d7846b06.png
[04175356_gn4o.jpg]: /images/20220113/4923db03d3454783b2ffcb57e05b55c8.png