關于觸發器
現實開發中我們經常會遇到這種情況,比如添加、删除和修改資訊的時候需要記錄日志,我們就要在完成正常的資料庫邏輯操作之後再去寫入日志表,這樣變成了兩步操作,更複雜了。
又比如删除一個人員資訊的時候,需要将他的購物記錄、收貨位址、收藏夾等都删了,這個連續的操作容易出錯,一緻性和完整性不好保證。這時候就要使用觸發器,既可以免去一堆的業務邏輯代碼,又能更好的保證資料完整性。
觸發器(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。
架構與思維·公衆号:撰稿者為bat、位元組的幾位高階研發/架構。不做廣告、不賣課、不要打賞,隻分享優質技術
碼字不易,歡迎關注,歡迎轉載
作者:翁智華
出處:https://www.cnblogs.com/wzh2010/
本文采用「CC BY 4.0」知識共享協定進行許可,轉載請注明作者及出處。