Mysql 之 触发器

MySQL 包含对触发程序的支持。触发程序是与有关的命名数据库对象。注意这里触发器关联的是表而不是像函数一样关联的是库。


语法格式:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt


触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。

trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER,以指明触发程序是在激活它的语句之前或之后触发。

trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:

    · INSERT:将新行插入表时激活触发程序,例如,通过INSERT、LOAD DATA和REPLACE语句。

    · UPDATE:更改某一行时激活触发程序,例如,通过UPDATE语句。

    · DELETE:从表中删除某一行时激活触发程序,例如,通过DELETE和REPLACE语句。


可能会造成混淆的例子之一是INSERT INTO .. ON DUPLICATE UPDATE语法:BEFORE INSERT触发程序对于每一行将激活,后跟AFTER INSERT触发程序,或BEFORE UPDATE和AFTER UPDATE触发程序,具体情况取决于行上是否有重复键。

对于具有相同触发程序动作时间和事件的给定表,不能有两个触发程序。例如,对于某一表,不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序,或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。

trigger_stmt是当触发程序激活时执行的语句。如果你打算执行多个语句,可使用BEGIN ... END复合语句结构。这样,就能使用存储子程序中允许的相同语句。


需要注意的:触发流程:BEFORE后会触发AFTER 及时是相同的trigger_event。

                    不能绑定具有同样事件的触发器:例如不能绑定两个AFTER  INSERT。


例子:

DELIMITER | -- 改变结束符

DROP TRIGGER IF EXISTS GenerateUS; -- 判断触发器是否存在 如果存在则删除GenerateUS触发器
CREATE TRIGGER GenerateUS AFTER INSERT ON ip -- 创建触发器。 条件: ip表触发了insert语句后(AFTER)执行下面的代码
    FOR EACH ROW BEGIN  -- FOR EACH ROW 每一行都触发 也叫行级触发。BEGIN是开始
        insert into ips(ip_id,gus) values(new.id,MD5(new.id)); -- 触发后执行的语句
    END -- 代码段结束
| -- 触发器结束 这句必须加,网上好多文章漏掉了这个自定义的结束符导致语法错误

DELIMITER ;  -- 改回结束符

这里为什么要改变结束符?由于Mysql的结束符是“;”。如果不改变的话,当你执行上述代码的时候是逐条执行的,而不是一个整体(Mysql将这个整体存入触发器)。这样的话就创建不了触发器,因为逐条语句执行会报句法错误。并且触发器内包含插入语句。所以必须要改变。


上述代码是将INSERT触发器绑定到了ip表上,并且在执行INSERT、LOAD DATA和REPLACE语句的时候出发插入语句,这条插入语句是将新插入的id和MD5加密后的id保存到了ips表。


原ip表:


+----+-----+-------+-------+-------+-------+-------+
| id | pid | name1 | name2 | name3 | name4 | name5 |
+----+-----+-------+-------+-------+-------+-------+
|  1 |   2 | 1     | 1     | 1     | 1     | 1     |
|  2 |   3 | 2     | 2     | 2     | 2     | 2     |
|  3 |   4 | 3     | 3     | 3     | 3     | 3     |
|  4 |   5 | 4     | 4     | 4     | 4     | 4     |
|  5 |   6 | 5     | 5     | 5     | 5     | 5     |
|  6 |   7 | 6     | 6     | 6     | 6     | 6     |
|  7 |   8 | 7     | 7     | 7     | 7     | 7     |
|  8 |   9 | 8     | 8     | 8     | 8     | 8     |
|  9 |  10 | 9     | 9     | 9     | 9     | 9     |
| 10 |  11 | 10    | 10    | 10    | 10    | 10    |
| 11 |  12 | 11    | 11    | 11    | 11    | 11    |
| 12 |   0 | 12    | 12    | 12    | 12    | 12    |
| 13 |  13 | 13    | 13    | 13    | 13    | 13    |
| 14 |  14 | 14    | 14    | 14    | 14    | 14    |
| 15 |  14 | 14    | 14    | 14    | 14    | 14    |
| 16 |  14 | 14    | 14    | 14    | 14    | 14    |
| 17 |  14 | 14    | 14    | 14    | 14    | 14    |
| 18 |  14 | 14    | 14    | 14    | 14    | 14    |
| 19 |  14 | 14    | 14    | 14    | 14    | 14    |
| 20 |  14 | 14    | 14    | 14    | 14    | 14    |
+----+-----+-------+-------+-------+-------+-------+


原ips表:

+----+-------+----------------------------------+
| id | ip_id | gus                              |
+----+-------+----------------------------------+
|  1 |     0 | cfcd208495d565ef66e7dff9f98764da |
|  2 |    14 | aab3238922bcc25a6f606eb525ffdc56 |
|  3 |    15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 |
|  4 |    16 | c74d97b01eae257e44aa9d5bade97baf |
|  5 |    17 | 70efdf2ec9b086079795c442636b55fb |
|  6 |    18 | 6f4922f45568161a8cdf4ad2299f6d23 |
|  7 |    19 | 1f0e3dad99908345f7439f8ffabdffc4 |
|  8 |    20 | 98f13708210194c475687be6106a3b84 |
+----+-------+----------------------------------+

执行一条插入语句:

INSERT INTO `ip` (`pid`, `name1`, `name2`, `name3`, `name4`, `name5`) VALUES ('14', '14', '14', '14', '14', '14')

新ip表:

+----+-----+-------+-------+-------+-------+-------+
| id | pid | name1 | name2 | name3 | name4 | name5 |
+----+-----+-------+-------+-------+-------+-------+
|  1 |   2 | 1     | 1     | 1     | 1     | 1     |
|  2 |   3 | 2     | 2     | 2     | 2     | 2     |
|  3 |   4 | 3     | 3     | 3     | 3     | 3     |
|  4 |   5 | 4     | 4     | 4     | 4     | 4     |
|  5 |   6 | 5     | 5     | 5     | 5     | 5     |
|  6 |   7 | 6     | 6     | 6     | 6     | 6     |
|  7 |   8 | 7     | 7     | 7     | 7     | 7     |
|  8 |   9 | 8     | 8     | 8     | 8     | 8     |
|  9 |  10 | 9     | 9     | 9     | 9     | 9     |
| 10 |  11 | 10    | 10    | 10    | 10    | 10    |
| 11 |  12 | 11    | 11    | 11    | 11    | 11    |
| 12 |   0 | 12    | 12    | 12    | 12    | 12    |
| 13 |  13 | 13    | 13    | 13    | 13    | 13    |
| 14 |  14 | 14    | 14    | 14    | 14    | 14    |
| 15 |  14 | 14    | 14    | 14    | 14    | 14    |
| 16 |  14 | 14    | 14    | 14    | 14    | 14    |
| 17 |  14 | 14    | 14    | 14    | 14    | 14    |
| 18 |  14 | 14    | 14    | 14    | 14    | 14    |
| 19 |  14 | 14    | 14    | 14    | 14    | 14    |
| 20 |  14 | 14    | 14    | 14    | 14    | 14    |
| 21 |  14 | 14    | 14    | 14    | 14    | 14    |
+----+-----+-------+-------+-------+-------+-------+

新ips表:

+----+-------+----------------------------------+
| id | ip_id | gus                              |
+----+-------+----------------------------------+
|  1 |     0 | cfcd208495d565ef66e7dff9f98764da |
|  2 |    14 | aab3238922bcc25a6f606eb525ffdc56 |
|  3 |    15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 |
|  4 |    16 | c74d97b01eae257e44aa9d5bade97baf |
|  5 |    17 | 70efdf2ec9b086079795c442636b55fb |
|  6 |    18 | 6f4922f45568161a8cdf4ad2299f6d23 |
|  7 |    19 | 1f0e3dad99908345f7439f8ffabdffc4 |
|  8 |    20 | 98f13708210194c475687be6106a3b84 |
|  9 |    21 | 3c59dc048e8850243be8079a5c74d079 |
+----+-------+----------------------------------+



加强版的触发器:

DELIMITER |

DROP TRIGGER IF EXISTS GenerateUS;
CREATE TRIGGER GenerateUS AFTER INSERT ON ip
FOR EACH ROW BEGIN
    SET @c = (SELECT gus FROM `ips` WHERE `ip_id` = new.id LIMIT 0, 1000);
     IF(ISNULL(@c)) THEN
       insert into ips(ip_id,gus) values(new.id,MD5(new.id));
     END IF;
END
|

DELIMITER ;



Mysql 之 触发器


本站如无特别说明即为原创,转而告知:(http://iwonmo.com/archives/1388.html)

标签: mysql, 触发器, mysql 触发器

添加新评论