触发器语法和示例
要创建触发器或删除触发器,使用CREATE TRIGGER
或DROP 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_sumBEFORE INSERT ON account FOR EACH ROW SET @sum = @sum +NEW .amount; Query OK, 0 rows affected (0.01 sec)
该CREATE TRIGGER
语句创建一个ins_sum
与account
表关联的名为触发器。它还包括一些子句,这些子句指定触发器的动作时间,触发事件以及触发器激活时的操作:
- 关键字
BEFORE
表示触发动作时间。在这种情况下,触发器将在插入表中的每一行之前激活。另一个允许的关键字是AFTER
。 - 关键字
INSERT
表示触发事件;即激活触发器的操作类型。在该示例中,INSERT
操作导致触发器激活。您还可以为DELETE
和UPDATE
操作创建触发器。 - 以下语句
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 @sumAS 'Total amount inserted'; +----------------------- + | Total amount inserted | +----------------------- + | 1852.48 | +----------------------- +
在这种情况下,语句执行@sum
后的值为或。INSERT
14.98 + 1937.50 - 100
1852.48
要销毁触发器,请使用一条DROP TRIGGER
语句。如果触发器不在默认架构中,则必须指定架构名称:
mysql>DROP TRIGGER test.ins_sum;
如果删除表,该表的所有触发器也将被删除。
触发器名称存在于模式名称空间中,这意味着所有触发器在模式内必须具有唯一的名称。不同架构中的触发器可以具有相同的名称。
可以为给定的表定义具有相同触发事件和动作时间的多个触发。例如,BEFORE UPDATE
一个表可以有两个触发器。默认情况下,具有相同触发事件和动作时间的触发将按照其创建顺序进行激活。要影响触发器的顺序,请在其后指定一个子句,该子句FOR EACH ROW
指示FOLLOWS
或,PRECEDES
并且还具有相同的触发器事件和动作时间的现有触发器的名称。使用FOLLOWS
,新触发器将在现有触发器之后激活。使用PRECEDES
,新触发器将在现有触发器之前激活。
例如,以下触发器定义BEFORE INSERT
为account
表定义了另一个触发器:
mysql>CREATE TRIGGER ins_transactionBEFORE INSERT ON account FOR EACH ROW PRECEDES ins_sumSET @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
。
在触发器主体内,使用OLD
and NEW
关键字可以访问受触发器影响的行中的列。OLD
并且NEW
是触发器的MySQL扩展;它们不区分大小写。
在INSERT
触发器中,只能使用;没有旧的行。在触发器中,只能使用;没有新行。在触发器中,您可以用来在更新前引用行的列,并在更新后引用行的列。NEW.col_name
DELETE
OLD.col_name
UPDATE
OLD.col_name
NEW.col_name
名为的列OLD
是只读的。您可以引用它(如果有SELECT
特权),但不能修改它。NEW
如果您有SELECT
权限,可以引用名为的列。在BEFORE
触发器中,如果有特权,也可以使用更改它的值。这意味着您可以使用触发器来修改要插入到新行中或用于更新行的值。(这样的语句在触发器中无效,因为行更改已经发生。)SET NEW.col_name= value
UPDATE
SET
AFTER
在一个BEFORE
触发器中,NEW
对于一个值AUTO_INCREMENT
列是0,实际上没有被插入新行时自动生成的序列号。
通过使用该BEGIN ... END
构造,您可以定义执行多个语句的触发器。在该BEGIN
块中,您还可以使用存储的例程(如条件和循环)中允许的其他语法。但是,就像存储例程一样,如果使用mysql程序定义执行多个语句的触发器,则必须重新定义mysql语句定界符,以便可以;
在触发器定义中使用语句定界符。下面的示例说明了这些要点。它定义了一个UPDATE
触发器,用于检查用于更新每一行的新值,并将该值修改为介于0到100之间BEFORE
的值。这必须是触发器,因为必须先检查该值才能用于更新行:
mysql>delimiter // mysql>CREATE TRIGGER upd_checkBEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW .amount < 0THEN SET NEW .amount = 0;ELSEIF NEW .amount > 100THEN SET NEW .amount = 100;END IF ;END ;// mysql>delimiter ;
分别定义存储过程,然后使用简单的CALL
语句从触发器调用存储过程会更容易。如果要在多个触发器中执行相同的代码,这也将非常有利。
触发器在激活后执行的语句中可能出现的内容有一些限制:
- 触发器不能使用该
CALL
语句来调用将数据返回到客户端或使用动态SQL的存储过程。(允许存储过程通过OUT
或INOUT
参数将数据返回给触发器。) - 触发不能使用语句或明或暗地开始或结束交易,如
START TRANSACTION
,COMMIT
或ROLLBACK
。(ROLLBACK to SAVEPOINT
允许,因为它不会结束交易。)。
另请参见“对存储程序的限制”。
MySQL在触发器执行过程中按以下方式处理错误:
- 如果
BEFORE
触发器失败,则不会执行相应行上的操作。 - 甲
BEFORE
触发由被激活的尝试插入或修改的行,而不管的尝试是否成功随后。 - 一个
AFTER
只有当任何触发器执行BEFORE
触发器和行操作成功执行。 BEFORE
或AFTER
触发器期间的错误会导致导致触发器调用的整个语句失败。- 对于事务表,语句失败应导致该语句执行的所有更改的回滚。触发器失败会导致语句失败,因此触发器失败也会导致回滚。对于非事务表,无法执行这种回滚,因此,尽管该语句失败,但在错误点之前执行的任何更改仍然有效。
触发器可以按名称包含对表的直接引用,例如testref
本示例中显示的命名触发器:
CREATE TABLE test1(a1 INT);CREATE TABLE test2(a2 INT);CREATE TABLE test3(a3 INT NOT NULLAUTO_INCREMENT PRIMARY KEY );CREATE TABLE test4( a4 INT NOT NULLAUTO_INCREMENT PRIMARY KEY , b4 INTDEFAULT 0 );delimiter |CREATE TRIGGER testrefBEFORE INSERT ON test1FOR EACH ROW BEGIN INSERT INTO test2SET a2 =NEW .a1;DELETE FROM test3WHERE a3 =NEW .a1;UPDATE test4SET b4 = b4 + 1WHERE 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 test1VALUES (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)