MYSQL5.7 触发器语法和例子

MYSQL5.7 触发器语法和例子

创建和删除触发器使用CREATE TRIGGER或DROP TRIGER语句:

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

这有一个由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_sum的触发器,作用于account表上。它包含的子句有:动作时间,触发事件和触发后的相应动作:

  • 关键字BEFORE指示了触发器的时间。本例中触发动作发生在插入表之前。相应的关键字是AFTER。
  • 关键字INSERT指示触发事件;INSERT这种操作可以激活触发器。你同样能建立DELETE和UPDATE操作的触发器。
  • FOR EACH ROW语句定义了触发体;触发体在每次触发器被激活时做用于对应的记录。在本例中,触发体只是把amount的数值累加到一起,然后放到用户变量sum中。NEW.amount的意思是“刚插入到表的新记录的amount列”。

使用触发器,设置累加器变量的值为0,执行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的值在INSERT之后会执行14.98+1937.50-100。

删除触发器使用:DROP TRIGGER语句。必须指定数据库名,除非已经有了默认数据库。

如果你删除了表,那么和表相关的触发器也会被删除。

触发器名存在于数据库模型的名字空间中,这意味着在同一个数据库下的所有触发器必须有不同的名字。

MYSQL 5.7.2中,可以为一个表定义多个相同事件和触发时间的触发器。例如,你可以有两个BEFORE UPDATE触发器放在同一个表上。默认情况下,具有相同触发事件和时间的触发器会按创建的先后顺序被激活使用。如果要改变触发器顺序,要为FOR EACH ROW命令指定FOLLOWS或PRECEDES,这样相同触发事件和时间的触发器就可以按指定的顺序执行。FOLLOWS:新建的触发器后执行;PRECEDES:新建的触发器先执行。

例如:下面为account表建立另一个BEFORE INSERT触发器

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,但是累加器@deposits和@withdrawals是分开的。它使用了PRECEDES子句,导致它在ins_sum之前执行;如果没有这个子句,就会在ins_sum之后执行,因为它在ins_sum之后创建的。

在MYSQL5.7.2之前,不能为一个表建立同样触发事件和时间和多个触发器。例如,你不能为同一个表建立两个BEFORE UPDATE。为了绕开这个问题,你可以定义一个触发器,在FOR EACH ROW中使用BEGIN … END执行多个语句块来实现。

在触发体中,OLDNEW关键字允许你访问被触发器作用的那一行记录。OLD和NEW是MYSQL对触发器的扩展。它们是大小写不敏感的。

INSERT TRIGGER中只有NEW.col_name能被使用;没有OLD记录。在DELETE TRIGGER中,只有OLD.col_name能被使用;没有NEW记录。在UPDATE TRIGGER中,可以使用OLD.col_name指向记录被修改之前的行,而NEW.col_name指向记录被修改之后的行。

被命名为OLD的记录是只读的。如果有SELECT权限就可以读它们,但不能修改。只要有SELECT权限就可以指向NEW的记录。在BEFORE 触发器中,只要有UPDATE权限就可以改变NEW的值,SET NEW.COL_NAME =VALUE。这意味着你能使用触发器修改插入或更新到表中的新记录。(SET不能用于AFTER触发器上,因为改变已经发生了。)

在BEFORE触发器中,用于AUTO_INCREAMENT属性列的NEW是0,不会获得自动增长产生的新值。

通过使用BEGIN … END结构,可以定义一个执行多行语句的触发器。在BEGIN块中,你能像使用在其它例程(routine)一样使用条件和循环。但是,就如存储的例程一样,如果你使用mysql客户端定义出发器,去执行多条语句,需要重新定义换行符(delimiter命令)。下面的例子说明了这一点。它定义了一个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中(dynamic SQL),但触发器允许存储过程有返回值,OUT或INOUT类型的参数。
  • 触发器不能显示或隐示的开启或结束一个事务,如:START TRANSACTION, COMMIT或ROLLBACK。(但ROLLBACK TO SAVEPOINT是可以使用的,因为它并没有结束一个事务)See also Section C.1, “Restrictions on Stored Programs”.

MYSQL的触发器执行期间处理错误的流程如下:

  • 如果BEFORE触发器失败,操作的相应记录不会被执行。
  • 一个BEFORE触发器由attempt激活插入或修改行,无论成功于否都会被忽略。
  • AFTER触发器只有当BEFORE触发器执行成功后才会执行。
  • An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.
  • 对于事务表,语句失败将会导致所有执行的语句回滚。触发器失败导至语句失败,所以触发器失败同样导致回滚。对于非事务表,不能执行回滚,所以虽然语句失败,但已经生效的语句也不会回滚。

触发器能直接通过表名引用某一个表,例如:

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

那么,test1,test2,test3,test4几个表中的值变化如下:

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)

发表评论