MySQL觸發(fā)器的應用示例詳解

更新時間：2022年08月02日 10:04:49 作者：逆輪回

這篇文章主要介紹了MySQL觸發(fā)器的應用,觸發(fā)器是與MySQL數(shù)據(jù)表有關的數(shù)據(jù)庫對象,在滿足定義條件時觸發(fā),并執(zhí)行觸發(fā)器中定義的語句集合,觸發(fā)器的這種特性可以協(xié)助應用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性,需要的朋友可以參考下

基本語法

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 語句創(chuàng)建觸發(fā)器。

語法格式如下

CREATE <觸發(fā)器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<觸發(fā)器主體>

語法說明如下

觸發(fā)器名

觸發(fā)器的名稱，觸發(fā)器在當前數(shù)據(jù)庫中必須具有唯一的名稱。如果要在某個特定數(shù)據(jù)庫中創(chuàng)建，名稱前面應該加上數(shù)據(jù)庫的名稱。

INSERT | UPDATE | DELET

觸發(fā)事件，用于指定激活觸發(fā)器的語句的種類。

注意：三種觸發(fā)器的執(zhí)行時間如下。

INSERT：將新行插入表時激活觸發(fā)器。例如，INSERT 的 BEFORE 觸發(fā)器不僅能被 MySQL 的 INSERT 語句激活，也能被 LOAD DATA 語句激活。

DELETE：從表中刪除某一行數(shù)據(jù)時激活觸發(fā)器，例如 DELETE 和 REPLACE 語句。

UPDATE：更改表中某一行數(shù)據(jù)時激活觸發(fā)器，例如 UPDATE 語句。

BEFORE | AFTER

BEFORE 和 AFTER，觸發(fā)器被觸發(fā)的時刻，表示觸發(fā)器是在激活它的語句之前或之后觸發(fā)。若希望驗證新數(shù)據(jù)是否滿足條件，則使用 BEFORE 選項；若希望在激活觸發(fā)器的語句執(zhí)行之后完成幾個或更多的改變，則通常使用 AFTER 選項。

表名

與觸發(fā)器相關聯(lián)的表名，此表必須是永久性表，不能將觸發(fā)器與臨時表或視圖關聯(lián)起來。在該表上觸發(fā)事件發(fā)生時才會激活觸發(fā)器。同一個表不能擁有兩個具有相同觸發(fā)時刻和事件的觸發(fā)器。例如，對于一張數(shù)據(jù)表，不能同時有兩個 BEFORE UPDATE 觸發(fā)器，但可以有一個 BEFORE UPDATE 觸發(fā)器和一個 BEFORE INSERT 觸發(fā)器，或一個 BEFORE UPDATE 觸發(fā)器和一個 AFTER UPDATE 觸發(fā)器。

觸發(fā)器主體

觸發(fā)器動作主體，包含觸發(fā)器激活時將要執(zhí)行的 MySQL 語句。如果要執(zhí)行多個語句，可使用 BEGIN…END 復合語句結構。

FOR EACH ROW

一般是指行級觸發(fā)，對于受觸發(fā)事件影響的每一行都要激活觸發(fā)器的動作。例如，使用 INSERT 語句向某個表中插入多行數(shù)據(jù)時，觸發(fā)器會對每一行數(shù)據(jù)的插入都執(zhí)行相應的觸發(fā)器動作。

注意：每個表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 與 AFTER，因此每個表最多支持 6 個觸發(fā)器。每個表的每個事件每次只允許有一個觸發(fā)器。單一觸發(fā)器不能與多個事件或多個表關聯(lián)。

另外，在 MySQL 中，若需要查看數(shù)據(jù)庫中已有的觸發(fā)器，則可以使用 SHOW TRIGGERS 語句。

創(chuàng)建BEFORE類型觸發(fā)器

在 test_db 數(shù)據(jù)庫中，數(shù)據(jù)表 tb_emp8 為員工信息表，包含 id、name、deptId 和 salary 字段，數(shù)據(jù)表 tb_emp8 的表結構如下所示。

mysql> SELECT * FROM tb_emp8;
Empty set (0.07 sec)
mysql> DESC tb_emp8;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  | UNI | NULL    |       |
| deptId | int(11)     | NO   | MUL | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)

【實例 1】創(chuàng)建一個名為 SumOfSalary 的觸發(fā)器，觸發(fā)的條件是向數(shù)據(jù)表 tb_emp8 中插入數(shù)據(jù)之前，對新插入的 salary 字段值進行求和計算。輸入的 SQL 語句和執(zhí)行過程如下所示。

mysql> CREATE TRIGGER SumOfSalary
    -> BEFORE INSERT ON tb_emp8
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.salary;
Query OK, 0 rows affected (0.35 sec)

觸發(fā)器 SumOfSalary 創(chuàng)建完成之后，向表 tb_emp8 中插入記錄時，定義的 sum 值由 0 變成了 1500，即插入值 1000 和 500 的和，如下所示。

SET @sum=0;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tb_emp8
    -> VALUES(1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 1500 |
+------+
1 row in set (0.03 sec)

創(chuàng)建AFTER類型觸發(fā)器

在 test_db 數(shù)據(jù)庫中，數(shù)據(jù)表 tb_emp6 和 tb_emp7 都為員工信息表，包含 id、name、deptId 和 salary 字段，數(shù)據(jù)表 tb_emp6 和 tb_emp7 的表結構如下所示。

mysql> SELECT * FROM tb_emp6;
Empty set (0.07 sec)
mysql> SELECT * FROM tb_emp7;
Empty set (0.03 sec)
mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> DESC tb_emp7;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

【實例 2】創(chuàng)建一個名為 double_salary 的觸發(fā)器，觸發(fā)的條件是向數(shù)據(jù)表 tb_emp6 中插入數(shù)據(jù)之后，再向數(shù)據(jù)表 tb_emp7 中插入相同的數(shù)據(jù)，并且 salary 為 tb_emp6 中新插入的 salary 字段值的 2 倍。輸入的 SQL 語句和執(zhí)行過程如下所示。

mysql> CREATE TRIGGER double_salary
    -> AFTER INSERT ON tb_emp6
    -> FOR EACH ROW
    -> INSERT INTO tb_emp7
    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
Query OK, 0 rows affected (0.25 sec)

觸發(fā)器 double_salary 創(chuàng)建完成之后，向表 tb_emp6 中插入記錄時，同時向表 tb_emp7 中插入相同的記錄，并且 salary 字段為 tb_emp6 中 salary 字段值的 2 倍，如下所示。

mysql> INSERT INTO tb_emp6
    -> VALUES (1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb_emp6;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   1000 |
|  2 | B    |      1 |    500 |
+----+------+--------+--------+
3 rows in set (0.04 sec)
mysql> SELECT * FROM tb_emp7;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | A    |      1 |   2000 |
|  2 | B    |      1 |   1000 |
+----+------+--------+--------+
2 rows in set (0.06 sec)

到此這篇關于MySQL觸發(fā)器的應用示例詳解的文章就介紹到這了,更多相關MySQL觸發(fā)器內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: