MySQL 觸發器的使用及需要註意的地方

Posted on by

關於觸發器

現實開發中我們經常會遇到這種情況，比如添加、刪除和修改信息的時候需要記錄日志，我們就要在完成常規的數據庫邏輯操作之後再去寫入日志表，這樣變成瞭兩步操作，更復雜瞭。

又比如刪除一個人員信息的時候，需要將他的購物記錄、收貨地址、收藏夾等都刪瞭，這個連續的操作容易出錯，一致性和完整性不好保證。這時候就要使用觸發器，既可以免去一堆的業務邏輯代碼，又能更好的保證數據完整性。

觸發器（trigger）是一種與表有關的數據庫對象：在滿足定義條件時觸發某種操作，並執行觸發器中定義的語句集合。觸發器的這種特性可以協助應用在數據庫端保證數據的完整性。

它是一種與表事件相關的特殊的存儲過程，當對一個表進行操作（ insert，delete， update）時就會激活執行。

觸發器的使用

創建觸發器

創建觸發器的語法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON t_name FOR EACH ROW trigger_stmt

說明：

trigger_name：觸發器名稱

tirgger_time：觸發執行時間點，數據操作前(BEFORE) 或者 數據操作後(AFTER)

trigger_event：觸發事件，增(INSERT)、刪(DELETE)、改(UPDATE)

t_name：指的是在 t_name 這張表上建立觸發器

trigger_stmt：觸發器的程序體，可以是一條SQL語句或者是用BEGIN和END包含的多條語句

觸發器隻能創建在永久表（Permanent）上，不能創建在臨時表（Temporary）上

FOR EACH ROW 固定表達式，表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器

tirgger_time有兩種類型，trigger_event有三種類型，所以他們一共有6種組合：BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE,AFTER INSERT,AFTER DELETE,AFTER UPDATE

示例（先創建一個日志表，用於觸發器觸發時候填入值）：

/*先創建一個日志表，用於觸發器觸發時候填入值*/
mysql> DROP TABLE IF EXISTS `TriggerLog`;
Query OK, 0 rows affected

mysql> CREATE TABLE `TriggerLog`
(
  `id` INT auto_increment PRIMARY KEY,
  `trigger_time` VARCHAR(30),
  `trigger_event` VARCHAR(30),
  `memo` VARCHAR(200)
);
Query OK, 0 rows affected

Insert類型觸發器：

mysql>
/*這邊聲明SQL腳本的結束符為// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_insert;
CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END //
Query OK, 0 rows affected

mysql>
/*重置SQL腳本的結束符為; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*插入一條數據 */
insert into students(studentname,score,classid) values('trigger1',100,0);
Query OK, 1 row affected


mysql>
/*查詢日志表看是否有觸發寫入 */
select * from `TriggerLog`;
+----+--------------+---------------+------------------------+
| id | trigger_time | trigger_event | memo          |
+----+--------------+---------------+------------------------+
| 1 | after    | insert    | new student info,id:21 |
+----+--------------+---------------+------------------------+
1 row in set

Update類型觸發器：

mysql>
/*這邊聲明SQL腳本的結束符為// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_update;
CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char)));
END //
Query OK, 0 rows affected

mysql>
/*重置SQL腳本的結束符為; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*更新數據 */
update students set score=99 where studentname='trigger1';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
/*查詢日志表看是否更新的時候有觸發寫入 */
select * from `TriggerLog`;
+----+--------------+---------------+---------------------------+
| id | trigger_time | trigger_event | memo           |
+----+--------------+---------------+---------------------------+
| 1 | after    | insert    | new student info,id:21  |
| 2 | after    | update    | update student info,id:21 |
+----+--------------+---------------+---------------------------+
2 rows in set

Delete類型觸發器：

mysql>
/*這邊聲明SQL腳本的結束符為// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_delete;
CREATE TRIGGER trig_after_delete AFTER DELETE ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char)));
END //
Query OK, 0 rows affected

mysql>
/*重置SQL腳本的結束符為; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/* 刪除數據 */
delete from students where studentid=21;
Query OK, 1 row affected

mysql>
/*查詢日志看刪除是否有觸發寫入*/
select * from `TriggerLog`;
+----+--------------+---------------+---------------------------+
| id | trigger_time | trigger_event | memo           |
+----+--------------+---------------+---------------------------+
| 1 | after    | insert    | new student info,id:21  |
| 2 | after    | update    | update student info,id:21 |
| 3 | after    | update    | delete student info,id:21 |
+----+--------------+---------------+---------------------------+
3 rows in set

查看觸發器

查看全部觸發器

show triggers; --語法 
mysql> show triggers;
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger      | Event | Table  | Statement                                                                             | Timing | Created | sql_mode                  | Definer    | character_set_client | collation_connection | Database Collation |
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
| trig_after_insert | INSERT | students | BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END  | AFTER | NULL  | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8         | utf8_general_ci   | latin1_swedish_ci |
| trig_after_update | UPDATE | students | BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char)));
END | AFTER | NULL  | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8         | utf8_general_ci   | latin1_swedish_ci |
| trig_after_delete | DELETE | students | BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char)));
END | AFTER | NULL  | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8         | utf8_general_ci   | latin1_swedish_ci |
+-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
3 rows in set

查看觸發器的創建語句

show create trigger trigger_name;  --語法
mysql> show create trigger trig_after_insert;
+-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Trigger      | sql_mode                  | SQL Original Statement                                                                                                                      | character_set_client | collation_connection | Database Collation |
+-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| trig_after_insert | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char)));
END | utf8         | utf8_general_ci   | latin1_swedish_ci |
+-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set

刪除觸發器

drop trigger trigger_name;  --語法 
mysql> drop trigger trig_after_insert;
Query OK, 0 rows affected
 
mysql> show create trigger trig_after_insert;
1360 - Trigger does not exist 

使用時的註意點

NEW / OLD的使用區別

觸發器針對的是數據庫中的每一行記錄，每行數據在操作前後都會有一個對應的狀態，觸發器將沒有操作之前的狀態保存到 old 關鍵字中，將操作後的狀態保存到 new 中

 new.cname --新增行（或修改前的行）的某列數據
 old.cname --刪除行（或修改後的行）的某列數據

需要註意的是，old 和 new 不是所有觸發器都有

觸發器類型 new和old的使用
INSERT型觸發器 沒有 old，隻有 new，new 表示將要(插入前)或者已經增加(插入後)的數據
UPDATE型觸發器 既有 old 也有 new，old 表示更新之前的數據，new 表示更新之後的數據
DELETE型觸發器 沒有 new，隻有 old，old 表示將要(刪除前)或者已經被刪除(刪除後)的數據

其實我們上面已經用過 new/old 來創建觸發器並獲取數據，我們根據這個表格重新來修改下那個更新觸發器(trig_after_update),輸出修改前和修改後的內容比較：

mysql>
/*這邊聲明SQL腳本的結束符為// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_update;
CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW
BEGIN
 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('from:',old.studentname,',',old.score,' ','to:',new.studentname,',',new.score));
END //
Query OK, 0 rows affected

mysql>
/*重置SQL腳本的結束符為; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*更新成績和名稱 */
update students set score=106,studentname='trigger2' where studentid=17;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
/*根據old和new 對比更新前後的值 */
select * from `TriggerLog`;
+----+--------------+---------------+--------------------------------------+
| id | trigger_time | trigger_event | memo                 |
+----+--------------+---------------+--------------------------------------+
| 1 | after    | insert    | new student info,id:21        |
| 2 | after    | update    | update student info,id:21      |
| 3 | after    | update    | delete student info,id:21      |
| 4 | after    | update    | from:test2,101.00 to:trigger2,106.00 |
+----+--------------+---------------+--------------------------------------+
4 rows in set

無法觸發對同表的修改

MySQL 的觸發器中不能對本表進行 insert、update 和 delete 操作，否則會報錯

mysql>
/*這邊聲明SQL腳本的結束符為// */
DELIMITER //
DROP TRIGGER IF EXISTS trig_after_insert;
CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW
BEGIN
 update students set score = score+1 where studentid= new.studentid;
END //
Query OK, 0 rows affected

mysql>
/*重置SQL腳本的結束符為; */
DELIMITER ;
Query OK, 0 rows affected

mysql>
/*插入一條數據之後因為觸發瞭對本表的修改，所以報錯瞭 */
insert into students(studentname,score,classid) values('trigger2',101,0);
1442 - Can't update table 'students' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

小結

1、觸發器可以通過數據庫中的關聯表實現級聯更改，即一張表數據的改變會影響其他表的數據，如數據修改，數據統計，數據復制等。
2、可以保證數據安全，並進行安全校驗，限制用戶具有操作數據庫的某種權利。
3、可以對復雜邏輯的實現進行數據完整性檢查和約束。
4、使用觸發器的要應需而用，如果過分依賴觸發器，會影響數據庫的結構，增加數據庫的執行和維護成本。
5、觸發器有BEFORE觸發器和AFTER觸發器的卻別，所以它的執行步驟是先執行 BEFORE觸發器，再執行業務腳本，最後執行AFTER觸發器。需要註意的是 某一個步驟錯誤後就不再執行瞭， 如果是事務表，會被回滾，如果是非事務表，那麼就無法回滾瞭，數據可能有不一致性。

觸發器的兩種限制

1、觸發程序不能調用將數據返回客戶端的存儲程序，也不能使用采用CALL語句的動態SQL語句，但是允許存儲程序通過參數將數據返回觸發程序，也就是存儲過程或者函數通過OUT或者INOUT類型的參數將數據返回觸發器是可以的，但是不能調用直接返回數據的過程。
2、不能再觸發器中使用以顯示或隱式方式開始或結束事務的語句，如START TRANS-ACTION,COMMIT或ROLLBACK。

以上就是MySQL 觸發器的使用及需要註意的地方的詳細內容，更多關於MySQL 觸發器的資料請關註WalkonNet其它相關文章！

推薦閱讀：