mysql 触发器

深藏阁楼爱情的钟 2024-04-25 20:15 1阅读 0赞

学习了[mysql 视图][mysql]，接着学习触发器

### 1，创建触发器 ###

触发器（trigger)是个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时候,就会被MySQL自动调用。比如当对fruits 表进行操作(INSERT、DELETE或UPDATE)时就会激活它执行。

触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如：可以根据客户当前的账户状态，控制是否允许插入新订单。本节将介绍如何创建触发器。

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

创建一个触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name 
    FOR EACH ROW trigger_stmt

其中 trigger\_name标识触发器名称，用户自行指定；trigger\_time标识触发时机，可以指定为before或after；trigger\_event标识触发事件，包括INSERT、 UPDATE和 DELETE；tbl\_name标识建立触发器的表名，即在哪张表上建立触发器；trigger\_stmt是触发器执行语句。

【例1】创建一个单执行语句的触发器，代码如下：

CREATE TABLE account (acct_num INT,amount DECIMAL (10,2));
    CREATE TRIGGER ins_sum BEFORE INSERT ON account
    FOR EACH ROW SET @sum = @sum + NEW.amount;

首先创建一个account表，表中有两个字段，分别为： acct\_num字段(定义为int类型)，amount字段（定义成浮点类型)﹔其次创建一个名为 ins\_sum的触发器，触发的条件是向数据表account插入数据之前，对新插入的amount字段值进行求和计算。

代码执行如下：

SET @sum=0;
    INSERT INTO account VALUES(1,1.00),(2,2.00) ;
    
    
    SELECT @sum;

首先创建一个account表，在向表account插入数据之前，计算所有新插入的account 表的amount值之和，触发器的名称为ins\_sum，条件是在向表插入数据之前触发。

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

创建多个执行语句的触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl name FOR EACH ROW
    BEGIN
      语句执行列表
     END

其中 trigger\_name标识触发器的名称，用户自行指定；trigger\_time标识触发时机，可以指定为before或after；rigger\_event标识触发事件，包括INSERT、UPDATE 和 DELETE；tbl\_name标识建立触发器的表名，即在哪张表上建立触发器；触发器程序可以使用BEGIN和END作为开始和结束，中间包含多条语句。

关键字：

:NEW 和:OLD使用方法和意义，new 只出现在insert和update时，old只出现在update和delete时。在insert时new表示新插入的行数据，update时new表示要替换的新数据、old表示要被更改的原来的数据行，delete时old表示要被删除的数据。

【例2】创建一个包含多个执行语句的触发器，代码如下：

CREATE TABLE test1(a1 INT);
    CREATE TABLE test2(a2 INT);
    CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
    
    
    CREATE TABLE test4(
      a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      b4 INT DEFAULT 0
    );
    
    
    DELIMITER //
    CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW 
    BEGIN
      INSERT INTO test2 SET a2 = NEW.a1;
      DELETE FROM test3 WHERE a3 = NEW.a1;
      UPDATE test4 SET b4 = b4+1 WHERE a4 = NEW.a1;
    END //
     
    INSERT INTO test3(a3) VALUES
    (NULL), (NULL), (NULL), (NULL), (NULL),
    (NULL),(NULL), (NULL), (NULL), (NULL);
    INSERT INTO test4(a4) VALUES
    (0), (0),(0),(0), (0),
    (0), (0),(0),(0), (0);

上面的代码是创建了一个名为testref的触发器，这个触发器的触发条件是在向表test1插入数据前执行触发器的语句，具体执行的代码如下：

INSERT INTO test1 VALUES
     (1), (3), (1), (7), (1), (8),(4),(4);

那么4个表中的数据如下：

SELECT * FROM test1;

![113f00caba6b443ab2332aa5a58d5e12.png][]

SELECT * FROM test2;

![3342d27e14f44027bf0a8abf26c39094.png][]

SELECT * FROM test3;

![3ab0a5183f3f43a0915fc166ff5a8f46.png][]

SELECT * FROM test4;

![e04a6248b88344f4a14a825b0eb2656b.png][]

执行结果显示，在向表test1插入记录的时候，test2、 test3、 test4 都发生了变化。从这个例子看INSERT触发了触发器，向test2中插入了test1 中的值，删除了test3 中相同的内容,

同时更新了test4 中的b4,即与插入的值相同的个数。

### 2，查看触发器 ###

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等。可以通过命令

来查看已经创建的触发器。本节将介绍两种查看触发器的方法，分别是： **SHOW TRIGGERS**

和在triggers表中查看触发器信息。

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

通过SHOW TRIGGERS查看触发器的语句如下：

SHOW TRIGGERS;

\[例3\]通过SHOW TRIGGERS命令查看-一个触发器，代码如下： .

SHOW TRIGGERS;

创建一一个简单的触发器，名称为trig. \_update, 每次向account 表更新数据之后都会向名称为myevent的数据表中插入-条记录，数据表myevent定义如下：

CREATE TABLE myevent(
      id int(11) DEFAULT NULL,
      evt_name char (20) DEFAULT NULL
    );

创建触发器的执行代码如下：

CREATE TRIGGER trig_update AFTER UPDATE ON account
    FOR EACH ROW INSERT INTO myevent VALUES (1, 'after update');

使用SHOW TRIGGERS命令查看触发器：

SHOW TRIGGERS;

![0385461060d04ee8ad5a6bdc8ff41acc.png][]

PS：如果用命令行，可以看到，信息显示比较混乱。如果在SHOW TRIGGERS命令的后面添加上“\\G’，显示信息会比较有条理，执行情况如下：

SHOW TRIGGERS \G

Trigger表示触发器的名称在这里两个触发器的名称分别为ins\_sum 和trig\_update；Event

表示激活触发器的事件这里的两个触发事件为插入操作INSERT和更新操作UPDATE; Table

表示激活触发器的操作对象表，这里都为account表;Timing表示触发器触发的时间，分别为

插入操作之前(BEFORE)和更新操作之后(AFTER) ; Staterment 表示触发器执行的操作，

还有一些其他信息，比如SQL的模式、触发器的定义账户和字符集等，这里不再一一介绍。

提示：SHOW TRIGGERS语句查看当前创建的所有触发器信息，在触发器较少的情况下，使用该语句会很方便。如果要查看特定触发器的信息，可以直接从information, schema 数据库

中的tiggers表中查找。

#### 2，在triggers表中查看触发器信息 ####

在MySQL中所有触发器的定义都存在INFORMATION SCHEMA数据库的TRIGGERS

表格中，可以通过查询命令SELECT来查看，具体的语法如下：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;

\[例4\]通过SELECT命令查看触发器，代码如下：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME= 'trig_update';

![0139df25db1b4412be904839a17a6531.png][]

上述的命令是通过WHERE来指定查看特定名称的触发器，下面是指定触发器名称的执行情况：

从上面的执行结果可以得到：TRIGGERSCHEMA表示触发器所在的数据库；

TRIGGER\_NAME后面是触发器的名称；EVENT\_OBJECT\_\_TABLE表示在哪个数据表上触

发ACTION\_STATEMENT 表示触发器触发的时候执行的具体操作ACTION\_ORIENTATION

是ROW，表示在每条记录上都触发；ACTION\_\_TIMING表示触发的时刻是AFTER，剩下的

是和系统相关的信息。

也可以不指定触发器名称，这样将查看所有的触发器，命令如下：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

这个命令会显示这个TRIGGERS表中所有的触发器信息。

### 3，触发器的使用 ###

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。

触发程序与表相关，当对表执行INSERT. DELETE 或UPDATE语句时，将激活触发程序。可以将触发程序设置为在执行语句之前或之后激活。例如,可以在从表中删除每一行之前，或在更新每一行之后激活触发程序。

\[例5\]创建一个在account表插入记录之后，更新myevent数据表的触发器，代码如下：

CREATE TRIGGER trig_insert AFTER INSERT ON account
    FOR EACH ROW INSERT INTO myevent VALUES (2, 'after insert');

上面的代码创建了一个trig. \_insert 的触发器在向表account插入数据之后会向表myevent

插入一组数据，代码执行如下：

INSERT INTO account VALUES (1, 1.00), (2, 2.00);
    
    
    SELECT * FROM myevent;

![ebc38da7296f40f09fb5753d52cf31da.png][]

从执行的结果来看，是创建了一个名称为trig\_insert 的触发器，它是在向account 插入记

录之后进行触发，执行的操作是向表myevent 插入一条记录。

### 4，删除触发器. ###

使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器，删除触发器语句基本

语法格式如下：

DROP TRIGGER [schema_name.] trigger_name

其中，schema\_name表示数据库名称，是可选的。如果省略了schema,将从当前数据库中舍弃触发程序： trigger\_name 是要删除的触发器的名称。

\[例6\]删除-一个触发器，代码如下：

DROP TRIGGER test.trig_insert;

上面的代码中test是触发器所在的数据库，trig\_insert是一个触发器的名称。代码执行如下：

![4598418ca8a04fd1a5ccddac9acedda6.png][]

触发器trig\_insert删除成功。

### 5，疑问解答 ###

疑问1 ：使用触发器时须特别注意。

在使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一一个触发器，比如对

表account创建了一个BEFOREINSERT触发器,那么如果对表account再次创建一个BEFORE

INSERT触发器，MySQL将会报错，此时，只可以在表account.上创建AFTER INSERT或者

BEFOREUPDATE类型的触发器。灵活地运用触发器将为操作省去很多麻烦。

疑问2 ：及时删除不再需要的触发器。

触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。如果需求

发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句,从而会影

响新的数据的完整性。因此，要将不再使用的触发器及时删除。

### 总结： ###

看起来触发器挺好用的，但实际的话，感觉触发器基本没用，要处理也基本是代码去处理相应的逻辑，这样也更好维护。

上一篇： 《[mysql 视图][mysql]》

下一篇： 《[mysql 性能优化][mysql 1]》

[mysql]: https://blog.csdn.net/qq_35461948/article/details/137117479
[113f00caba6b443ab2332aa5a58d5e12.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/aec884f21fec4aa08d877245c63ef843.png
[3342d27e14f44027bf0a8abf26c39094.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/49cd1f40c09b40ed81bcd5bf58dccf86.png
[3ab0a5183f3f43a0915fc166ff5a8f46.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/1de40a7d9166451c951909d3db4328e3.png
[e04a6248b88344f4a14a825b0eb2656b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/3b56a15ec744420486aeec05bca83612.png
[0385461060d04ee8ad5a6bdc8ff41acc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/3f628bd06d614c059b3004ed18b9900a.png
[0139df25db1b4412be904839a17a6531.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/0675357e443d483494a8d6a122d60fb4.png
[ebc38da7296f40f09fb5753d52cf31da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/e04f5354ad524e3a9599d07dd18adfab.png
[4598418ca8a04fd1a5ccddac9acedda6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/25/4b84d1fe2272400cad37759d1b62c9d2.png
[mysql 1]: https://blog.csdn.net/qq_35461948/article/details/137436376?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22137436376%22%2C%22source%22%3A%22qq_35461948%22%7D