• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 触发器语法和示例

    要创建触发器或删除触发器,使用CREATE TRIGGERDROP TRIGGER声明,在描述第13.1.22,“CREATE TRIGGER语句”,和第13.1.34,“DROP TRIGGER语句”。

    这是一个简单的示例,将触发器与表相关联,以激活INSERT操作。触发器充当累加器,将插入表中各列之一的值相加。

    mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
           FOR EACH ROW SET @sum = @sum + NEW.amount;
    Query OK, 0 rows affected (0.01 sec)
    

    CREATE TRIGGER语句创建一个ins_sumaccount表关联的名为触发器。它还包括一些子句,这些子句指定触发器的动作时间,触发事件以及触发器激活时的操作:

    • 关键字BEFORE表示触发动作时间。在这种情况下,触发器将在插入表中的每一行之前激活。另一个允许的关键字是AFTER
    • 关键字INSERT表示触发事件;即激活触发器的操作类型。在该示例中,INSERT操作导致触发器激活。您还可以为DELETEUPDATE操作创建触发器。
    • 以下语句FOR EACH ROW定义了触发器主体;也就是说,每当触发触发器时执行的语句,对于受触发事件影响的每一行都会发生一次。在该示例中,触发器主体很简单SET,它将插入amount列中的值累积到用户变量中。该语句引用该列,因为NEW.amount它表示“要插入到新行中的amount列的值。”

    要使用触发器,请将accumulator变量设置为零,执行一条INSERT语句,然后参见该变量之后的值:

    mysql> SET @sum = 0;
    mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
    mysql> SELECT @sum AS 'Total amount inserted';
    +-----------------------	+
    | Total amount inserted	|
    +-----------------------	+
    |               1852.48	|
    +-----------------------	+
    

    在这种情况下,语句执行@sum后的值为或。INSERT14.98 + 1937.50 - 1001852.48

    要销毁触发器,请使用一条DROP TRIGGER语句。如果触发器不在默认架构中,则必须指定架构名称:

    mysql> DROP TRIGGER test.ins_sum;
    

    如果删除表,该表的所有触发器也将被删除。

    触发器名称存在于模式名称空间中,这意味着所有触发器在模式内必须具有唯一的名称。不同架构中的触发器可以具有相同的名称。

    可以为给定的表定义具有相同触发事件和动作时间的多个触发。例如,BEFORE UPDATE一个表可以有两个触发器。默认情况下,具有相同触发事件和动作时间的触发将按照其创建顺序进行激活。要影响触发器的顺序,请在其后指定一个子句,该子句FOR EACH ROW指示FOLLOWS或,PRECEDES并且还具有相同的触发器事件和动作时间的现有触发器的名称。使用FOLLOWS,新触发器将在现有触发器之后激活。使用PRECEDES,新触发器将在现有触发器之前激活。

    例如,以下触发器定义BEFORE INSERTaccount表定义了另一个触发器:

    mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
           FOR EACH ROW PRECEDES ins_sum
           SET
           @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
           @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
    Query OK, 0 rows affected (0.01 sec)
    

    该触发器ins_transaction与相似,ins_sum但分别存储存款和取款。它有一个PRECEDES使它在之前激活的子句ins_sum;如果没有该子句,它将ins_sum在之后激活,因为它是在之后创建的ins_sum

    在触发器主体内,使用OLDand NEW关键字可以访问受触发器影响的行中的列。OLD并且NEW是触发器的MySQL扩展;它们不区分大小写。

    INSERT触发器中,只能使用;没有旧的行。在触发器中,只能使用;没有新行。在触发器中,您可以用来在更新前引用行的列,并在更新后引用行的列。NEW.col_nameDELETEOLD.col_nameUPDATEOLD.col_nameNEW.col_name

    名为的列OLD是只读的。您可以引用它(如果有SELECT特权),但不能修改它。NEW如果您有SELECT权限,可以引用名为的列。在BEFORE触发器中,如果有特权,也可以使用更改它的值。这意味着您可以使用触发器来修改要插入到新行中或用于更新行的值。(这样的语句在触发器中无效,因为行更改已经发生。)SET NEW.col_name= valueUPDATESETAFTER

    在一个BEFORE触发器中,NEW对于一个值AUTO_INCREMENT列是0,实际上没有被插入新行时自动生成的序列号。

    通过使用该BEGIN ... END构造,您可以定义执行多个语句的触发器。在该BEGIN块中,您还可以使用存储的例程(如条件和循环)中允许的其他语法。但是,就像存储例程一样,如果使用mysql程序定义执行多个语句的触发器,则必须重新定义mysql语句定界符,以便可以;在触发器定义中使用语句定界符。下面的示例说明了这些要点。它定义了一个UPDATE触发器,用于检查用于更新每一行的新值,并将该值修改为介于0到100之间BEFORE的值。这必须是触发器,因为必须先检查该值才能用于更新行:

    mysql> delimiter //
    mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
           FOR EACH ROW
           BEGIN
               IF NEW.amount < 0 THEN
                   SET NEW.amount = 0;
               ELSEIF NEW.amount > 100 THEN
                   SET NEW.amount = 100;
               END IF;
           END;//
    mysql> delimiter ;
    

    分别定义存储过程,然后使用简单的CALL语句从触发器调用存储过程会更容易。如果要在多个触发器中执行相同的代码,这也将非常有利。

    触发器在激活后执行的语句中可能出现的内容有一些限制:

    • 触发器不能使用该CALL语句来调用将数据返回到客户端或使用动态SQL的存储过程。(允许存储过程通过OUTINOUT参数将数据返回给触发器。)
    • 触发不能使用语句或明或暗地开始或结束交易,如START TRANSACTIONCOMMITROLLBACK。(ROLLBACK to SAVEPOINT允许,因为它不会结束交易。)。

    另请参见“对存储程序的限制”。

    MySQL在触发器执行过程中按以下方式处理错误:

    • 如果BEFORE触发器失败,则不会执行相应行上的操作。
    • BEFORE触发由被激活的尝试插入或修改的行,而不管的尝试是否成功随后。
    • 一个AFTER只有当任何触发器执行BEFORE触发器和行操作成功执行。
    • BEFOREAFTER触发器期间的错误会导致导致触发器调用的整个语句失败。
    • 对于事务表,语句失败应导致该语句执行的所有更改的回滚。触发器失败会导致语句失败,因此触发器失败也会导致回滚。对于非事务表,无法执行这种回滚,因此,尽管该语句失败,但在错误点之前执行的任何更改仍然有效。

    触发器可以按名称包含对表的直接引用,例如testref本示例中显示的命名触发器:

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

    结果,这四个表包含以下数据:

    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)
    

    下篇:触发元数据