首页 > 代码库 > MYSQL触发器

MYSQL触发器

一、触发器CREATE语法:

【定义】

    触发程序是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对象。

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

【语法】

    CREATE   TRIGGER   trigger_name    trigger_time   trigger_event

      ON    tb1_name     FOR   EACH    ROW     ttigger_stmt

【语法说明】

    trigger_name:用户自定义触发器名称。

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

    trigger_event:指定触发器被激活的语句类型。类型如下:

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

       UPDATE:更改某一行数据时激活触发器,例如通过UPDATE语句。

       DELETE:删除某一行数据时激活触发器,例如通过DELETE和REPLACE语句。

    tb1_name:触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。

    ttigger_stmt:当触发器被激活时所要执行的操作语句。如果是多条语句,可以使用BEGIN...END复合语句结构。

【示例】

    CREATE TABLE tb_test1(al   INT);

    CREATE TABLE tb_test2(a2   INT);

    CREATE TABLE tb_test3(a3   INT   NOT NULL AUTO_INCREMENT PRIMARY KEY);

    CREATE TABLE tb_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$

    DELIMITER;

    

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

  

如果将下述值插入表test1,如下所示:

    mysql> INSERT INTO test1 VALUES 
        -> (1), (3), (1), (7), (1), (8), (4), (4);
    Query OK, 8 rows affected (0.01 sec)
    Records: 8  Duplicates: 0  Warnings: 0
    
    那么4个表中的数据如下:
    mysql> SELECT * FROM test1;
    +------+
    | a1   |
    +------+
    |    1 |
    |    3 |
    |    1 |
    |    7 |
    |    1 |
    |    8 |
    |    4 |
    |    4 |
    +------+
    8 rows in set (0.00 sec)
 
    mysql> SELECT * FROM test2;
    +------+
    | a2   |
    +------+
    |    1 |
    |    3 |
    |    1 |
    |    7 |
    |    1 |
    |    8  |
    |    4 |
    |    4 |
    +------+
    8 rows in set (0.00 sec)
 
    mysql> SELECT * FROM test3;
    +----+
    | a3 |
    +----+
    |  2 |
    |  5 |
    |  6 |
    |  9 |
    | 10 |
    +----+
    5 rows in set (0.00 sec)
 
    mysql> SELECT * FROM test4;
    +----+------+
    | a4 | b4   |
    +----+------+
    |  1 |    3 |
    |  2 |    0 |
    |  3 |    1 |
    |  4 |    2 |
    |  5 |    0 |
    |  6 |    0 |
    |  7 |    1 |
    |  8 |    1 |
    |  9 |    0 |
    | 10 |    0 |
    +----+------+
    10 rows in set (0.00 sec)

    使用别名OLD和NEW,能够引用与触发器相关表中的列。OLD.col_name在更新或删除之前,引用已有行中的1列。NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。

    激活触发器时,对于触发器引用所有的OLD和NEW列,需要具有SELECT权限,对于作为SET赋值目标的所有NEW列,需要UPDATE权限。

    注*:CREATE TRIGGER语句需要SUPER权限。

二:DROP TRIGGER语法

    DROP   TRIGGER   [schema_name]    trigger_name

    删除触发器。方案名称(schema_name)可选。如果省略schema_name,将从当前方案中舍弃触发器。

    注*:CREATE TRIGGER语句需要SUPER权限。

三:其他

    触发器不能调用将数据返回客户端的存储程序、也不能使用采用CALL语句的动态SQL(允许存储程序通过参数将数据返回触发程序)。

    触发器不能使用以显示或隐试方式开始或节数事务的语句,如START TRANSACTION、COMMIT或ROLLBACK。

    

    使用OLD和NEW关键字,能够访问受触发程序影响的行中的列(OLD和NEW不区分大小写)。在INSERT触发程序中,仅能使用NEW.col_name,没有旧行。

    在DELETE触发程序中,仅能使用OLD.col_name,没有新行。在UPDATE触发程序中,可以使用OLD.col_name来引用更新前的某一行的列,也能使用NEW.col_name来引用更新后的行中的列。

    用OLD命名的列是只读的。你可以引用它,但不能更改它。对于用NEW命名的列,如果具有SELECT权限,可引用它。在BEFORE触发程序中,如果你具有UPDATE权限,可使用“SET                        NEW.col_name = value”更改它的值。这意味着,你可以使用触发程序来更改将要插入到新行中的值,或用于更新行的值。

    在BEFORE触发程序中,AUTO_INCREMENT列的NEW值为0,不是实际插入新记录时将自动生成的序列号。

    

    在触发程序的执行过程中,MySQL处理错误的方式如下:

    ·         如果BEFORE触发程序失败,不执行相应行上的操作。

    ·         仅当BEFORE触发程序(如果有的话)和行操作均已成功执行,才执行AFTER触发程序。

    ·         如果在BEFORE或AFTER触发程序的执行过程中出现错误,将导致调用触发程序的整个语句的失败。

    ·         对于事务性表,如果触发程序失败(以及由此导致的整个语句的失败),该语句所执行的所有更改将回滚。对于非事务性表,不能执行这类回滚,因而,即使语句失败,

        失败之前所作的任何更改依然有效。

 

MYSQL触发器