mysql 触发器

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

1，创建触发器

        触发器（trigger)是个特别的存储过程，差别的是，执行存储过程要利用CALL语句来调用，而触发器的执行不需要利用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生的时间,就会被MySQL自动调用。好比当对fruits 表举行操作(INSERT、DELETE或UPDATE)时就会激活它执行。
        触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。比方：可以根据客户当前的账户状态，控制是否允许插入新订单。本节将先容怎样创建触发器。

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

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

1. CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name
2. FOR EACH ROW trigger_stmt

复制代码

        其中 trigger_name标识触发器名称，用户自行指定；trigger_time标识触发时机，可以指定为before或after；trigger_event标识触发事件，包括INSERT、 UPDATE和 DELETE；tbl_name标识创建触发器的表名，即在哪张表上创建触发器；trigger_stmt是触发器执行语句。
        【例1】创建一个单执行语句的触发器，代码如下：

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

复制代码

        首先创建一个account表，表中有两个字段，分别为： acct_num字段(定义为int范例)，amount字段（定义成浮点范例)﹔其次创建一个名为 ins_sum的触发器，触发的条件是向数据表account插入数据之前，对新插入的amount字段值举行求和计算。
        代码执行如下：

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

复制代码

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

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

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

1. CREATE TRIGGER trigger_name trigger_time trigger_event
2. ON tbl name FOR EACH ROW
3. BEGIN
4.   语句执行列表
5. 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】创建一个包含多个执行语句的触发器，代码如下：

1. CREATE TABLE test1(a1 INT);
2. CREATE TABLE test2(a2 INT);
3. CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
6. CREATE TABLE test4(
7.   a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
8.   b4 INT DEFAULT 0
9. );
12. DELIMITER //
13. CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW
14. BEGIN
15.   INSERT INTO test2 SET a2 = NEW.a1;
16.   DELETE FROM test3 WHERE a3 = NEW.a1;
17.   UPDATE test4 SET b4 = b4+1 WHERE a4 = NEW.a1;
18. END //
20. INSERT INTO test3(a3) VALUES
21. (NULL), (NULL), (NULL), (NULL), (NULL),
22. (NULL),(NULL), (NULL), (NULL), (NULL);
23. INSERT INTO test4(a4) VALUES
24. (0), (0),(0),(0), (0),
25. (0), (0),(0),(0), (0);

复制代码

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

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

复制代码

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

1. SELECT * FROM test1;

复制代码

1. SELECT * FROM test2;

复制代码

1. SELECT * FROM test3;

复制代码

1. SELECT * FROM test4;

复制代码

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

2，检察触发器

        检察触发器是指检察数据库中已存在的触发器的定义、状态和语法信息等。可以通过下令
来检察已经创建的触发器。本节将先容两种检察触发器的方法，分别是： SHOW TRIGGERS
和在triggers表中检察触发器信息。

1，SHOW TRIGGERS语句检察触发器信息

        通过SHOW TRIGGERS检察触发器的语句如下：

1. SHOW TRIGGERS;

复制代码

        [例3]通过SHOW TRIGGERS下令检察-一个触发器，代码如下： .

1. SHOW TRIGGERS;

复制代码

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

1. CREATE TABLE myevent(
2.   id int(11) DEFAULT NULL,
3.   evt_name char (20) DEFAULT NULL
4. );

复制代码

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

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

复制代码

        利用SHOW TRIGGERS下令检察触发器：

1. SHOW TRIGGERS;

复制代码

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

1. 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来检察，具体的语法如下：

1. SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;

复制代码

        [例4]通过SELECT下令检察触发器，代码如下：

1. 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，剩下的
是和系统相关的信息。
        也可以不指定触发器名称，这样将检察所有的触发器，下令如下：

1. SELECT * FROM INFORMATION_SCHEMA.TRIGGERS;

复制代码

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


3，触发器的利用

        触发程序是与表有关的定名数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于查抄插入到表中的值，或对更新涉及的值举行计算。
        触发程序与表相关，当对表执行INSERT. DELETE 或UPDATE语句时，将激活触发程序。可以将触发程序设置为在执行语句之前或之后激活。比方,可以在从表中删除每一行之前，或在更新每一行之后激活触发程序。
        [例5]创建一个在account表插入记录之后，更新myevent数据表的触发器，代码如下：

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

复制代码

        上面的代码创建了一个trig. _insert 的触发器在向表account插入数据之后会向表myevent
        插入一组数据，代码执行如下：

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

复制代码

        从执行的结果来看，是创建了一个名称为trig_insert 的触发器，它是在向account 插入记
录之后举行触发，执行的操作是向表myevent 插入一条记录。

4，删除触发器.

        利用DROP TRIGGER语句可以删除MySQL中已经定义的触发器，删除触发器语句根本
语法格式如下：

1. DROP TRIGGER [schema_name.] trigger_name

复制代码

        其中，schema_name表示数据库名称，是可选的。假如省略了schema,将从当前数据库中舍弃触发程序： trigger_name 是要删除的触发器的名称。
        [例6]删除-一个触发器，代码如下：

1. 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 性能优化》

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。