MySQL 觸發(fā)器的使用及需要注意的地方
關(guān)于觸發(fā)器
現(xiàn)實(shí)開(kāi)發(fā)中我們經(jīng)常會(huì)遇到這種情況，比如添加、刪除和修改信息的時(shí)候需要記錄日志，我們就要在完成常規(guī)的數(shù)據(jù)庫(kù)邏輯操作之后再去寫(xiě)入日志表，這樣變成了兩步操作，更復(fù)雜了。
又比如刪除一個(gè)人員信息的時(shí)候，需要將他的購(gòu)物記錄、收貨地址、收藏夾等都刪了，這個(gè)連續(xù)的操作容易出錯(cuò)，一致性和完整性不好保證。這時(shí)候就要使用觸發(fā)器，既可以免去一堆的業(yè)務(wù)邏輯代碼，又能更好的保證數(shù)據(jù)完整性。
觸發(fā)器（trigger）是一種與表有關(guān)的數(shù)據(jù)庫(kù)對(duì)象：在滿(mǎn)足定義條件時(shí)觸發(fā)某種操作，并執(zhí)行觸發(fā)器中定義的語(yǔ)句集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫(kù)端保證數(shù)據(jù)的完整性。
它是一種與表事件相關(guān)的特殊的存儲(chǔ)過(guò)程，當(dāng)對(duì)一個(gè)表進(jìn)行操作（ insert，delete， update）時(shí)就會(huì)激活執(zhí)行。
觸發(fā)器的使用
創(chuàng)建觸發(fā)器
創(chuàng)建觸發(fā)器的語(yǔ)法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event ON t_name FOR EACH ROW trigger_stmt
說(shuō)明：
trigger_name：觸發(fā)器名稱(chēng)
tirgger_time：觸發(fā)執(zhí)行時(shí)間點(diǎn)，數(shù)據(jù)操作前(BEFORE) 或者 數(shù)據(jù)操作后(AFTER)
trigger_event：觸發(fā)事件，增(INSERT)、刪(DELETE)、改(UPDATE)
t_name：指的是在 t_name 這張表上建立觸發(fā)器
trigger_stmt：觸發(fā)器的程序體，可以是一條SQL語(yǔ)句或者是用BEGIN和END包含的多條語(yǔ)句
觸發(fā)器只能創(chuàng)建在永久表（Permanent）上，不能創(chuàng)建在臨時(shí)表（Temporary）上
FOR EACH ROW 固定表達(dá)式，表示任何一條記錄上的操作滿(mǎn)足觸發(fā)事件都會(huì)觸發(fā)該觸發(fā)器
tirgger_time有兩種類(lèi)型，trigger_event有三種類(lèi)型，所以他們一共有6種組合：BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE,AFTER INSERT,AFTER DELETE,AFTER UPDATE
示例（先創(chuàng)建一個(gè)日志表，用于觸發(fā)器觸發(fā)時(shí)候填入值）：
/*先創(chuàng)建一個(gè)日志表，用于觸發(fā)器觸發(fā)時(shí)候填入值*/ 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類(lèi)型觸發(fā)器：
mysql>
/*這邊聲明SQL腳本的結(jié)束符為// */
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腳本的結(jié)束符為; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*插入一條數(shù)據(jù) */
insert into students(studentname,score,classid) values('trigger1',100,0);
Query OK, 1 row affected
mysql>
/*查詢(xún)?nèi)罩颈砜词欠裼杏|發(fā)寫(xiě)入 */
select * from `TriggerLog`;
+----+--------------+---------------+------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+------------------------+
| 1 | after | insert | new student info,id:21 |
+----+--------------+---------------+------------------------+
1 row in set
Update類(lèi)型觸發(fā)器：
mysql>
/*這邊聲明SQL腳本的結(jié)束符為// */
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腳本的結(jié)束符為; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*更新數(shù)據(jù) */
update students set score=99 where studentname='trigger1';
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
/*查詢(xún)?nèi)罩颈砜词欠窀碌臅r(shí)候有觸發(fā)寫(xiě)入 */
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類(lèi)型觸發(fā)器：
mysql>
/*這邊聲明SQL腳本的結(jié)束符為// */
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腳本的結(jié)束符為; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/* 刪除數(shù)據(jù) */
delete from students where studentid=21;
Query OK, 1 row affected
mysql>
/*查詢(xún)?nèi)罩究磩h除是否有觸發(fā)寫(xiě)入*/
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
查看觸發(fā)器
查看全部觸發(fā)器
show triggers; --語(yǔ)法
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
查看觸發(fā)器的創(chuàng)建語(yǔ)句
show create trigger trigger_name; --語(yǔ)法
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
刪除觸發(fā)器
drop trigger trigger_name; --語(yǔ)法
mysql> drop trigger trig_after_insert; Query OK, 0 rows affected mysql> show create trigger trig_after_insert; 1360 - Trigger does not exist
使用時(shí)的注意點(diǎn)
NEW / OLD的使用區(qū)別
觸發(fā)器針對(duì)的是數(shù)據(jù)庫(kù)中的每一行記錄，每行數(shù)據(jù)在操作前后都會(huì)有一個(gè)對(duì)應(yīng)的狀態(tài)，觸發(fā)器將沒(méi)有操作之前的狀態(tài)保存到 old 關(guān)鍵字中，將操作后的狀態(tài)保存到 new 中
new.cname --新增行（或修改前的行）的某列數(shù)據(jù) old.cname --刪除行（或修改后的行）的某列數(shù)據(jù)
需要注意的是，old 和 new 不是所有觸發(fā)器都有
| 觸發(fā)器類(lèi)型 | new和old的使用 |
|---|---|
| INSERT型觸發(fā)器 | 沒(méi)有 old，只有 new，new 表示將要(插入前)或者已經(jīng)增加(插入后)的數(shù)據(jù) |
| UPDATE型觸發(fā)器 | 既有 old 也有 new，old 表示更新之前的數(shù)據(jù)，new 表示更新之后的數(shù)據(jù) |
| DELETE型觸發(fā)器 | 沒(méi)有 new，只有 old，old 表示將要(刪除前)或者已經(jīng)被刪除(刪除后)的數(shù)據(jù) |
其實(shí)我們上面已經(jīng)用過(guò) new/old 來(lái)創(chuàng)建觸發(fā)器并獲取數(shù)據(jù)，我們根據(jù)這個(gè)表格重新來(lái)修改下那個(gè)更新觸發(fā)器(trig_after_update),輸出修改前和修改后的內(nèi)容比較：
mysql>
/*這邊聲明SQL腳本的結(jié)束符為// */
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腳本的結(jié)束符為; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*更新成績(jī)和名稱(chēng) */
update students set score=106,studentname='trigger2' where studentid=17;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
/*根據(jù)old和new 對(duì)比更新前后的值 */
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
無(wú)法觸發(fā)對(duì)同表的修改
MySQL 的觸發(fā)器中不能對(duì)本表進(jìn)行 insert、update 和 delete 操作，否則會(huì)報(bào)錯(cuò)
mysql>
/*這邊聲明SQL腳本的結(jié)束符為// */
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腳本的結(jié)束符為; */
DELIMITER ;
Query OK, 0 rows affected
mysql>
/*插入一條數(shù)據(jù)之后因?yàn)橛|發(fā)了對(duì)本表的修改，所以報(bào)錯(cuò)了 */
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.
小結(jié)
1、觸發(fā)器可以通過(guò)數(shù)據(jù)庫(kù)中的關(guān)聯(lián)表實(shí)現(xiàn)級(jí)聯(lián)更改，即一張表數(shù)據(jù)的改變會(huì)影響其他表的數(shù)據(jù)，如數(shù)據(jù)修改，數(shù)據(jù)統(tǒng)計(jì)，數(shù)據(jù)復(fù)制等。
2、可以保證數(shù)據(jù)安全，并進(jìn)行安全校驗(yàn)，限制用戶(hù)具有操作數(shù)據(jù)庫(kù)的某種權(quán)利。
3、可以對(duì)復(fù)雜邏輯的實(shí)現(xiàn)進(jìn)行數(shù)據(jù)完整性檢查和約束。
4、使用觸發(fā)器的要應(yīng)需而用，如果過(guò)分依賴(lài)觸發(fā)器，會(huì)影響數(shù)據(jù)庫(kù)的結(jié)構(gòu)，增加數(shù)據(jù)庫(kù)的執(zhí)行和維護(hù)成本。
5、觸發(fā)器有BEFORE觸發(fā)器和AFTER觸發(fā)器的卻別，所以它的執(zhí)行步驟是先執(zhí)行 BEFORE觸發(fā)器，再執(zhí)行業(yè)務(wù)腳本，最后執(zhí)行AFTER觸發(fā)器。需要注意的是 某一個(gè)步驟錯(cuò)誤后就不再執(zhí)行了， 如果是事務(wù)表，會(huì)被回滾，如果是非事務(wù)表，那么就無(wú)法回滾了，數(shù)據(jù)可能有不一致性。
觸發(fā)器的兩種限制
1、觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶(hù)端的存儲(chǔ)程序，也不能使用采用CALL語(yǔ)句的動(dòng)態(tài)SQL語(yǔ)句，但是允許存儲(chǔ)程序通過(guò)參數(shù)將數(shù)據(jù)返回觸發(fā)程序，也就是存儲(chǔ)過(guò)程或者函數(shù)通過(guò)OUT或者INOUT類(lèi)型的參數(shù)將數(shù)據(jù)返回觸發(fā)器是可以的，但是不能調(diào)用直接返回?cái)?shù)據(jù)的過(guò)程。
2、不能再觸發(fā)器中使用以顯示或隱式方式開(kāi)始或結(jié)束事務(wù)的語(yǔ)句，如START TRANS-ACTION,COMMIT或ROLLBACK。
以上就是MySQL 觸發(fā)器的使用及需要注意的地方的詳細(xì)內(nèi)容，更多關(guān)于MySQL 觸發(fā)器的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
- MySQL觸發(fā)器使用詳解
- mysql觸發(fā)器(Trigger)簡(jiǎn)明總結(jié)和使用實(shí)例
- Mysql中的觸發(fā)器簡(jiǎn)單介紹及使用案例
- MySQL觸發(fā)器的使用
- mysql 觸發(fā)器創(chuàng)建與使用方法示例
- MySQL觸發(fā)器的使用場(chǎng)景及方法實(shí)例
- Oracle使用觸發(fā)器和mysql中使用觸發(fā)器的案例比較
- MySQL觸發(fā)器的使用和優(yōu)缺點(diǎn)介紹
- mysql觸發(fā)器簡(jiǎn)介、創(chuàng)建觸發(fā)器及使用限制分析
- MySql數(shù)據(jù)庫(kù)觸發(fā)器使用教程
相關(guān)文章
大家好，本篇文章主要講的是MySQL導(dǎo)入與導(dǎo)出備份詳解，感興趣的同學(xué)趕快來(lái)看一看吧，對(duì)你有幫助的話記得收藏一下，方便下次瀏覽2021-12-12
在mysql中如果我們要模糊查詢(xún)數(shù)據(jù)我們可以使用like帶%%號(hào)來(lái)實(shí)現(xiàn)查詢(xún)，下面我來(lái)簡(jiǎn)單的介紹一下關(guān)于mysql like使用方法2013-10-10
MySQL中B樹(shù)索引和B+樹(shù)索引的區(qū)別詳解
這篇文章主要為大家詳細(xì)介紹了MySQL中B樹(shù)索引和B+樹(shù)索引的區(qū)別，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來(lái)幫助2022-03-03
下面小編就為大家?guī)?lái)一篇MySQL常用時(shí)間函數(shù)詳解(推薦)。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2016-06-06
淺談innodb的索引頁(yè)結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引
下面小編就為大家?guī)?lái)一篇淺談innodb的索引頁(yè)結(jié)構(gòu),插入緩沖,自適應(yīng)哈希索引。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-03-03
mysql中關(guān)于between和in的區(qū)別
這篇文章主要介紹了mysql中關(guān)于between和in的區(qū)別說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-07-07
mysql 5.7.17 安裝配置方法圖文教程(CentOS7)
這篇文章主要為大家詳細(xì)介紹了CentOS7下mysql 5.7.17 安裝配置方法圖文教程，感興趣的小伙伴們可以參考一下2016-12-12
MySql分頁(yè)時(shí)使用limit+order by會(huì)出現(xiàn)數(shù)據(jù)重復(fù)問(wèn)題解決
在MySQL中我們通常會(huì)采用limit來(lái)進(jìn)行翻頁(yè)查詢(xún)，當(dāng)limit遇到 order by的時(shí)候會(huì)出現(xiàn)數(shù)據(jù)重復(fù)問(wèn)題，本文就來(lái)記錄一下，感興趣的可以了解一下2021-08-08
這篇文章主要介紹了Mysql使用簡(jiǎn)單教程(二)的相關(guān)資料,需要的朋友可以參考下2016-05-05
這篇文章主要介紹了一文詳解MySQL主從同步原理，MySQL主從同步是基于Bin?Log實(shí)現(xiàn)的，而B(niǎo)in?Log記錄的是原始SQL語(yǔ)句，更多相關(guān)內(nèi)容介紹感興趣的小伙伴可以參考一下2022-08-08