關於觸發器
現實開發中我們經常會遇到這種情況，比如新增、刪除和修改資訊的時候需要記錄日誌，我們就要在完成常規的資料庫邏輯操作之後再去寫入日誌表，這樣變成了兩步操作，更復雜了。
又比如刪除一個人員資訊的時候，需要將他的購物記錄、收貨地址、收藏夾等都刪了，這個連續的操作容易出錯，一致性和完整性不好保證。這時候就要使用觸發器，既可以免去一堆的業務邏輯程式碼，又能更好的保證資料完整性。
觸發器（trigger）是一種與表有關的資料庫物件：在滿足定義條件時觸發某種操作，並執行觸發器中定義的語句集合。觸發器的這種特性可以協助應用在資料庫端保證資料的完整性。
它是一種與表事件相關的特殊的儲存過程，當對一個表進行操作（ insert，delete， update）時就會啟用執行。
觸發器的使用
建立觸發器
建立觸發器的語法如下：
1 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
示例（先建立一個日誌表，用於觸發器觸發時候填入值）：
1 /*先建立一個日誌表，用於觸發器觸發時候填入值*/ 2 mysql> DROP TABLE IF EXISTS `TriggerLog`; 3 Query OK, 0 rows affected 4 5 mysql> CREATE TABLE `TriggerLog` 6 ( 7 `id` INT auto_increment PRIMARY KEY, 8 `trigger_time` VARCHAR(30), 9 `trigger_event` VARCHAR(30), 10 `memo` VARCHAR(200) 11 ); 12 Query OK, 0 rows affected
Insert型別觸發器：
1 mysql> 2 /*這邊宣告SQL指令碼的結束符為// */ 3 DELIMITER // 4 DROP TRIGGER IF EXISTS trig_after_insert; 5 CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW 6 BEGIN 7 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char))); 8 END // 9 Query OK, 0 rows affected 10 11 mysql> 12 /*重置SQL指令碼的結束符為; */ 13 DELIMITER ; 14 Query OK, 0 rows affected 15 16 mysql> 17 /*插入一條資料 */ 18 insert into students(studentname,score,classid) values('trigger1',100,0); 19 Query OK, 1 row affected 20 21 22 mysql> 23 /*查詢日誌表看是否有觸發寫入 */ 24 select * from `TriggerLog`; 25 +----+--------------+---------------+------------------------+ 26 | id | trigger_time | trigger_event | memo | 27 +----+--------------+---------------+------------------------+ 28 | 1 | after | insert | new student info,id:21 | 29 +----+--------------+---------------+------------------------+ 30 1 row in set
Update型別觸發器：
1 mysql> 2 /*這邊宣告SQL指令碼的結束符為// */ 3 DELIMITER // 4 DROP TRIGGER IF EXISTS trig_after_update; 5 CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW 6 BEGIN 7 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char))); 8 END // 9 Query OK, 0 rows affected 10 11 mysql> 12 /*重置SQL指令碼的結束符為; */ 13 DELIMITER ; 14 Query OK, 0 rows affected 15 16 mysql> 17 /*更新資料 */ 18 update students set score=99 where studentname='trigger1'; 19 Query OK, 1 row affected 20 Rows matched: 1 Changed: 1 Warnings: 0 21 22 mysql> 23 /*查詢日誌表看是否更新的時候有觸發寫入 */ 24 select * from `TriggerLog`; 25 +----+--------------+---------------+---------------------------+ 26 | id | trigger_time | trigger_event | memo | 27 +----+--------------+---------------+---------------------------+ 28 | 1 | after | insert | new student info,id:21 | 29 | 2 | after | update | update student info,id:21 | 30 +----+--------------+---------------+---------------------------+ 31 2 rows in set
Delete型別觸發器：
1 mysql> 2 /*這邊宣告SQL指令碼的結束符為// */ 3 DELIMITER // 4 DROP TRIGGER IF EXISTS trig_after_delete; 5 CREATE TRIGGER trig_after_delete AFTER DELETE ON students FOR EACH ROW 6 BEGIN 7 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char))); 8 END // 9 Query OK, 0 rows affected 10 11 mysql> 12 /*重置SQL指令碼的結束符為; */ 13 DELIMITER ; 14 Query OK, 0 rows affected 15 16 mysql> 17 /* 刪除資料 */ 18 delete from students where studentid=21; 19 Query OK, 1 row affected 20 21 mysql> 22 /*查詢日誌看刪除是否有觸發寫入*/ 23 select * from `TriggerLog`; 24 +----+--------------+---------------+---------------------------+ 25 | id | trigger_time | trigger_event | memo | 26 +----+--------------+---------------+---------------------------+ 27 | 1 | after | insert | new student info,id:21 | 28 | 2 | after | update | update student info,id:21 | 29 | 3 | after | update | delete student info,id:21 | 30 +----+--------------+---------------+---------------------------+ 31 3 rows in set
檢視觸發器
檢視全部觸發器
1 show triggers; --語法
1 mysql> show triggers; 2 +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ 3 | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | 4 +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ 5 | trig_after_insert | INSERT | students | BEGIN 6 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char))); 7 END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci | 8 | trig_after_update | UPDATE | students | BEGIN 9 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('update student info,id:',cast(new.studentid as char))); 10 END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci | 11 | trig_after_delete | DELETE | students | BEGIN 12 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('delete student info,id:',cast(old.studentid as char))); 13 END | AFTER | NULL | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8 | utf8_general_ci | latin1_swedish_ci | 14 +-------------------+--------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+ 15 3 rows in set
檢視觸發器的建立語句
1 show create trigger trigger_name; --語法
1 mysql> show create trigger trig_after_insert; 2 +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 3 | Trigger | sql_mode | SQL Original Statement | character_set_client | collation_connection | Database Collation | 4 +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 5 | trig_after_insert | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW 6 BEGIN 7 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','insert',concat('new student info,id:',cast(new.studentid as char))); 8 END | utf8 | utf8_general_ci | latin1_swedish_ci | 9 +-------------------+--------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ 10 1 row in set
刪除觸發器
1 drop trigger trigger_name; --語法
1 mysql> drop trigger trig_after_insert; 2 Query OK, 0 rows affected 3 4 mysql> show create trigger trig_after_insert; 5 1360 - Trigger does not exist
使用時的注意點
NEW / OLD的使用區別
觸發器針對的是資料庫中的每一行記錄，每行資料在操作前後都會有一個對應的狀態，觸發器將沒有操作之前的狀態儲存到 old 關鍵字中，將操作後的狀態儲存到 new 中
1 new.cname --新增行（或修改前的行）的某列資料 2 old.cname --刪除行（或修改後的行）的某列資料
需要注意的是，old 和 new 不是所有觸發器都有
| 觸發器型別 | new和old的使用 |
|---|---|
| INSERT型觸發器 | 沒有 old，只有 new，new 表示將要(插入前)或者已經增加(插入後)的資料 |
| UPDATE型觸發器 | 既有 old 也有 new，old 表示更新之前的資料，new 表示更新之後的資料 |
| DELETE型觸發器 | 沒有 new，只有 old，old 表示將要(刪除前)或者已經被刪除(刪除後)的資料 |
其實我們上面已經用過 new/old 來建立觸發器並獲取資料，我們根據這個表格重新來修改下那個更新觸發器(trig_after_update),輸出修改前和修改後的內容比較：
1 mysql> 2 /*這邊宣告SQL指令碼的結束符為// */ 3 DELIMITER // 4 DROP TRIGGER IF EXISTS trig_after_update; 5 CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROW 6 BEGIN 7 insert into `TriggerLog`(`trigger_time`,`trigger_event`,`memo`) values ('after','update',concat('from:',old.studentname,',',old.score,' ','to:',new.studentname,',',new.score)); 8 END // 9 Query OK, 0 rows affected 10 11 mysql> 12 /*重置SQL指令碼的結束符為; */ 13 DELIMITER ; 14 Query OK, 0 rows affected 15 16 mysql> 17 /*更新成績和名稱 */ 18 update students set score=106,studentname='trigger2' where studentid=17; 19 Query OK, 1 row affected 20 Rows matched: 1 Changed: 1 Warnings: 0 21 22 mysql> 23 /*根據old和new 對比更新前後的值 */ 24 select * from `TriggerLog`; 25 +----+--------------+---------------+--------------------------------------+ 26 | id | trigger_time | trigger_event | memo | 27 +----+--------------+---------------+--------------------------------------+ 28 | 1 | after | insert | new student info,id:21 | 29 | 2 | after | update | update student info,id:21 | 30 | 3 | after | update | delete student info,id:21 | 31 | 4 | after | update | from:test2,101.00 to:trigger2,106.00 | 32 +----+--------------+---------------+--------------------------------------+ 33 4 rows in set
無法觸發對同表的修改
MySQL 的觸發器中不能對本表進行 insert、update 和 delete 操作，否則會報錯
1 mysql> 2 /*這邊宣告SQL指令碼的結束符為// */ 3 DELIMITER // 4 DROP TRIGGER IF EXISTS trig_after_insert; 5 CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROW 6 BEGIN 7 update students set score = score+1 where studentid= new.studentid; 8 END // 9 Query OK, 0 rows affected 10 11 mysql> 12 /*重置SQL指令碼的結束符為; */ 13 DELIMITER ; 14 Query OK, 0 rows affected 15 16 mysql> 17 /*插入一條資料之後因為觸發了對本表的修改，所以報錯了 */ 18 insert into students(studentname,score,classid) values('trigger2',101,0); 19 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。