MYSQL5.7 触发器语法和例子

2017年10月30日 skiron Comments 0 Comment

创建和删除触发器使用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执行多个语句块来实现。

在触发体中，OLD和NEW关键字允许你访问被触发器作用的那一行记录。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)

Hello World

New Begining

MYSQL5.7 触发器语法和例子

2017年10月30日 skiron Comments 0 Comment

发表回复取消回复

发表回复 取消回复

发表回复取消回复