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;

SELECT * FROM test2;

SELECT * FROM test3;

SELECT * FROM test4;

        执行结果显示，在向表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;

        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';

        上述的命令是通过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;

        从执行的结果来看，是创建了一个名称为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是一个触发器的名称。代码执行如下：

触发器trig_insert删除成功。

5，疑问解答

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

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

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

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

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

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

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

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

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

总结：

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

        上一篇： 《mysql 视图》

        下一篇： 《mysql 性能优化》