mysql

MySQL学习笔记（六）：触发器

2017-06-01  本文已影响135人  maxwellyue

触发器概念

什么是触发器

触发器是一种与表操作有关的数据库对象，当触发器所在表上发生指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
可以这样来理解：当某个表上的数据发生变化的时候，就会执行一个函数，这个函数可能会去执行一些对其他表的操作。这个函数就是触发器，它就相当于编程里的监听器一样，一旦监听到这个表发生了某些变化，就会执行已经写好的一套逻辑。按照面向对象的思想，这个触发器是该表的一个属性。

优缺点

参考：MySQL触发器的利弊

创建触发器

该部分参考CREATE TRIGGER Syntax和 Trigger Syntax and Examples

一个定义在表tbl_name上的触发器会按照trigger_order的顺序在该表记录发生了trigger_event的trigger_time时候，执行一套逻辑：trigger_body。

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

对以上创建语句：

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 ;
```
 * 在触发器的执行语句里，还有一些限制：
```
### 1
The trigger cannot use the CALL statement to invoke stored procedures 
that return data to the client 
or that use dynamic SQL.
 (Stored procedures are permitted to return data to the trigger through OUT
 or INOUT parameters.)
### 2
The trigger cannot use statements that explicitly or implicitly begin or end a transaction, 
such as START TRANSACTION, COMMIT, or ROLLBACK. 
(ROLLBACK to SAVEPOINT is permitted because it does not end a transaction.).
```

* 错误处理
 * 如果BEFORE类型的触发器出错，激活该触发器的列的操作不会执行。
 * BEFORE类型的触发器是由对列的插入或更新操作激活的，无论之后这些操作是否成功。
 * 只有BEFORE触发器和随后的更新列操作成功后，AFTER 触发器才会被激活。
 * 无论是BEFORE触发器还是AFTER 触发器出错，都会导致激活他们的那个操作失败。
 * 对事务表，触发器会导致回滚。但是对于非事务表，不支持回滚，所以，如果语句执行失败，在失败之前的语句仍会有效。

---
### 删除触发器
```
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
```
如果你执行`drop table`，那么该表的触发器也会同时被删除。


---

### 实例
例子1
```
# 准备工作
# 创建4张表，并在表test1上创建触发器testref
# 然后在表test3和表test4中初始化一些数据。
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插入数据，就会激活触发器testref
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)
```
上一篇 下一篇

猜你喜欢

热点阅读