文章目录

一、创建触发器
- 1、创建只有一个执行语句的触发器
- 2、创建有多个执行语句的触发器
- 3、NEW与OLD详解
二、查看触发器
- 1、SHOW TRIGGERS语句查看触发器信息
- 2、在information_schema.triggers表中查看触发器信息
三、删除触发器

官方文档

Q：什么是触发器？
A:　触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

在这里插入图片描述

触发器是MySQL响应DELETE/INSERT/UPDATE语句更改数据之前或之后自动执行的一条MySQL语句或者位置BEGIN和END语句之间的一组语句。当需要在某个表发生更改时自动处理时，可以创建一个触发器
触发器本身没有返回值，不过它们可以读取或者改变触发SQL语句所影响的数据
在MySQL5.7.2版本之前，每个表最多可以定义六个触发器。
- BEFORE INSERT - 在数据插入表之前被激活触发器。
- AFTER INSERT - 在将数据插入表之后激活触发器。
- BEFORE UPDATE - 在表中的数据更新之前激活触发器。
- AFTER UPDATE - 在表中的数据更新之后激活触发器。
- BEFORE DELETE - 在从表中删除数据之前激活触发器。
- AFTER DELETE - 从表中删除数据之后激活触发器。
单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义
两个触发器。
对每个表的每一个事件，最多只能定义一个触发器(换句话说，不能再after insert上定义两个触发器)
MYSQL只支持基于行的触发，也就是说，触发器始终是针对一条记录的。而不是针对整个SQL语句的。如果变更的数据集非常大的话，效率会很低。
触发器可能导致死锁和锁等待，如果触发器失败，原来的SQL语句也会失败。

触发器的特性：

有begin end体，begin end;之间的语句可以写的简单或者复杂
什么条件会触发：I、D、U什么条件会触发：I、D、U
什么时候触发：在增删改前或者后什么时候触发：在增删改前或者后
触发频率：针对每一行执行触发频率：针对每一行执行
触发器定义在表上，附着在表上。触发器定义在表上，附着在表上。

也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助应用在数据库端确保数据的完整性。

注意：cannot associate a trigger with a TEMPORARY table or a view.

！！尽量少使用触发器。

假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。因此我们特别需要注意的一点是触发器的begin end;之间的语句的执行效率一定要高，资源消耗要小。

触发器尽量少的使用，因为不管如何，它还是很消耗资源，如果使用的话要谨慎的使用，确定它是非常高效的：触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

一、创建触发器

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
　　[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后。

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。

tigger_event详解：
　　①INSERT型触发器：插入某一行时激活触发器，可能通过INSERT、LOAD DATA、REPLACE 语句触发(LOAD DAT语句用于将一个文件装入到一个数据表中，相当与一系列的INSERT操作)；
　　②UPDATE型触发器：更改某一行时激活触发器，可能通过UPDATE语句触发；
　　③DELETE型触发器：删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。

trigger_order是MySQL5.7之后的一个功能，用于定义多个触发器，使用follows(尾随)或precedes(在…之先)来选择触发器执行的先后顺序。

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

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

例1：创建了一个名为trig1的触发器，一旦在work表中有插入动作，就会自动往time表里插入当前时间

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());

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

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;

例2：定义一个触发器，一旦有满足条件的删除操作，就会执行BEGIN和END中的语句

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> 　　INSERT INTO time VALUES(NOW());
    -> 　　INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;

3、NEW与OLD详解

在触发器的SQL语句中，可以关联表中的任何列，通过使用OLD与NEW列名来标志，如OLD.col_name，NEW.col_name。OLD.col_name关联现有的行的一列的被更新或者删除之前的值。New.col_name关联一个新行的插入或者更新现有的行的一列的值。
①对于INSERT语句，只有NEW是合法的。否则会报错：ERROR 1363 (HY000): There is no OLD row in on INSERT trigger
②对于DELETE语句，只有OLD是合法的。否则会报错：ERROR 1363 (HY000): There is no NEW row in on DELETE trigger
③ 对于UPDATE语句，NEW和OLD可以同时使用。

另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

例3：

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    -> 　　IF NEW.amount < 0 THEN
    -> 　　　　SET NEW.amount = 0;
    -> 　　ELSEIF NEW.amount > 100 THEN
    -> 　　　　SET NEW.amount = 100;
    -> 　　END IF;
    -> END$$
mysql> delimiter ;
mysql> update account set amount=-10 where acct_num=137;
mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |    0.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+
mysql> update account set amount=200 where acct_num=137;
mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

【总结】

创建触发器的语法

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER 触发器名 BEFORE | AFTER INSERT | UPDATE | DELETE
ON 表名 FOR EACH ROW —定义触发执行间隔。FOR EACH ROW子句定义触发器每隔一行执行一次动作，而不是对整个表执行一次。
BEGIN
```
  trigger_body  --包含要触发执行的SQL语句。可以是任何合法的语句，包括复合语句(需要使用BEGIN ... END结构)，流控制语句(if、case、while、loop、for、repeat、leave、iterate)，变量声明(declare)以及指派(set)，异常处理声明，允许条件声明，但是这里的语句受的限制和函数的一样。
```
END

触发器名： —表的名字_触发器类型，最好确保数据库范围内使用唯一的触发器名
触发器的类型
- UPDATE
  - update 操作
- DELETE
  - delete 操作
  - replace 操作
    - **注意：**drop，truncate等DDL操作不会触发DELETE
- INSERT
  - insert 操作
  - load data 操作
  - replace 操作

二、查看触发器

1、SHOW TRIGGERS语句查看触发器信息

mysql> SHOW TRIGGERS from database_name; #列出数据库的触发器
mysql> SHOW CREATE TRIGGER trigger_name;  #查看创建触发器
mysql> SHOW TRIGGERS\G #列出所有触发器

结果，显示所有触发器的基本信息；无法查询指定的触发器。

2、在information_schema.triggers表中查看触发器信息

mysql> SELECT * FROM information_schema.triggers\G

结果，显示所有触发器的详细信息；同时，该方法可以查询制定触发器的详细信息。

mysql> select * from information_schema.triggers 
    -> where trigger_name='upd_check'\G;

Tips：

所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询。

三、删除触发器

mysql> DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
mysql> drop trigger trigger_name;

删除触发器之后最好使用上面的方法查看一遍；同时，也可以使用database.trig来指定某个数据库中的触发器。

Tips：

如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作，这很关键。

原文链接：https://www.cnblogs.com/geaozhang/p/6819648.html\#chuangjian

MySQL：触发器trigger的使用

文章目录

一、创建触发器

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

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

3、NEW与OLD详解

二、查看触发器

1、SHOW TRIGGERS语句查看触发器信息

2、在information_schema.triggers表中查看触发器信息

三、删除触发器

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Mysql（四）触发器 Trigger

相关 MySQL数据库触发器 trigger

相关一文搞懂MySQL触发器trigger的使用

相关 mysql之触发器trigger

相关 Mysql之触发器Trigger

相关 mysql触发器trigger笔记

相关 MySQL：触发器trigger的使用

相关 MySql 触发器（trigger）实例

相关 Mysql触发器trigger 实例详解

相关触发器（Trigger）

随便看看

DebugView 使用技巧

有空就看看的leetcode7——合并两个有序链表（c++版）

java 线程池的原理及使用

Cocos2d游戏开发学习记录——3.实现僵尸跳舞demo

使用JNI进行Java与C/C++语言混合编程

opencv制作微信小游戏最强连一连辅助(3)--opencv matchTemplete多目标匹配

教程文章

热评文章

1江湖小白之一起学Python （二）爬取数据的保存

2Java Shiro：简化身份验证和授权的安全框架

3Java中try()catch{}的使用方法

4Swagger注解-@ApiModel 和 @ApiModelProperty

5windows下强制杀死tomcat进程

6uni-app 条形码(一维码)/二维码生成实现

标签列表