MySQL觸發器的使用

概述：

當具體的表發生特定的數據庫事件時，觸發器執行對應的SQL命令。

語法：

創建觸發器的一般命令如下：

CREATE [temp|temporary] trigger name

[before|after] [insert|delete|update|update of columns] ON table

action

觸發器的使用：

創建：

mysql> CREATE TRIGGER stu_trigger AFTER INSERT
-> ON students
-> FOR EACH ROW
-> INSERT INTO info(stu_id, info) values (new.id, '');
Query OK, 0 rows affected (0.07 sec)

觸發事件：

mysql> INSERT INTO students(id, name, age) values (4, 'Zeus', 56400);
Query OK, 1 row affected (0.00 sec)

驗證students表結果：

mysql> SELECT * FROM students;
+------+----------------+--------+
| id | name | age |
+------+----------------+--------+
| 1 | bumblebee | 800 |
| 2 | king of monkey | 10000 |
| 3 | Medusa | 100000 |
| 4 | Zeus | 56400 |
+------+----------------+--------+
4 rows in set (0.00 sec)

驗證info表結果：

mysql> SELECT * FROM info;
+----+--------+---------------------------------+
| id | stu_id | info |
+----+--------+---------------------------------+
| 1 | 1 | A member of the deformed steel. |
| 2 | 2 | Hero in Chinese Mythology. |
| 3 | 3 | In Greek mythology the Gorgon. |
| 5 | 4 | |
+----+--------+---------------------------------+
4 rows in set (0.00 sec)

注：這裡的新id為5而不為4的原因，是因為之前對info表作了刪除操作導致。

查看觸發器的信息：

mysql> SELECT * FROM information_schema.triggers;
+----------------+--------------+--------------------+---------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_SCHEMA |
+----------------+--------------+--------------------+---------------------+
| student | stu_trigger | INSERT | student |
+----------------+--------------+--------------------+---------------------+


+--------------------+----------------------------------------------------+
| EVENT_OBJECT_TABLE | ACTION_STATEMENT |
+--------------------+----------------------------------------------------+
| students | INSERT INTO info(stu_id, info) values (new.id, '') |
+--------------------+----------------------------------------------------+

1 row in set (0.19 sec)

注：上面的打印信息是刪減版的，完整的信息，可以自行在環境中查看。

刪除觸發器：

mysql> DROP TRIGGER stu_trigger;
Query OK, 0 rows affected (0.01 sec)

驗證刪除結果：

mysql> SELECT * FROM information_schema.triggers;
Empty set (0.01 sec)

說明刪除成功了