現(xiàn)實開發(fā)中我們經(jīng)常會遇到這種情況，比如添加、刪除和修改信息的時候需要記錄日志，我們就要在完成常規(guī)的數(shù)據(jù)庫邏輯操作之后再去寫入日志表，這樣變成了兩步操作，更復(fù)雜了。

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

觸發(fā)器（trigger）是一種與表有關(guān)的數(shù)據(jù)庫對象：在滿足定義條件時觸發(fā)某種操作，并執(zhí)行觸發(fā)器中定義的語句集合。觸發(fā)器的這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫端保證數(shù)據(jù)的完整性。

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

創(chuàng)建觸發(fā)器的語法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON t_name FOR EACH ROW trigger_stmt

說明：

trigger_name：觸發(fā)器名稱

tirgger_time：觸發(fā)執(zhí)行時間點，數(shù)據(jù)操作前(BEFORE) 或者 數(shù)據(jù)操作后(AFTER)

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

t_name：指的是在 t_name 這張表上建立觸發(fā)器

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

觸發(fā)器只能創(chuàng)建在永久表（Permanent）上，不能創(chuàng)建在臨時表（Temporary）上

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

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

示例（先創(chuàng)建一個日志表，用于觸發(fā)器觸發(fā)時候填入值）：

/*先創(chuàng)建一個日志表，用于觸發(fā)器觸發(fā)時候填入值*/mysql> DROP TABLE IF EXISTS `TriggerLog`;Query OK, 0 rows affectedmysql> 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類型觸發(fā)器：

mysql>/*這邊聲明SQL腳本的結(jié)束符為// */DELIMITER //DROP TRIGGER IF EXISTS trig_after_insert;CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROWBEGIN 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 affectedmysql>/*重置SQL腳本的結(jié)束符為; */DELIMITER ;Query OK, 0 rows affectedmysql>/*插入一條數(shù)據(jù) */insert into students(studentname,score,classid) values(’trigger1’,100,0);Query OK, 1 row affectedmysql>/*查詢?nèi)罩颈砜词欠裼杏|發(fā)寫入 */select * from `TriggerLog`;+----+--------------+---------------+------------------------+| id | trigger_time | trigger_event | memo |+----+--------------+---------------+------------------------+| 1 | after | insert | new student info,id:21 |+----+--------------+---------------+------------------------+1 row in set

Update類型觸發(fā)器：

mysql>/*這邊聲明SQL腳本的結(jié)束符為// */DELIMITER //DROP TRIGGER IF EXISTS trig_after_update;CREATE TRIGGER trig_after_update AFTER UPDATE ON students FOR EACH ROWBEGIN 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 affectedmysql>/*重置SQL腳本的結(jié)束符為; */DELIMITER ;Query OK, 0 rows affectedmysql>/*更新數(shù)據(jù) */update students set score=99 where studentname=’trigger1’;Query OK, 1 row affectedRows matched: 1 Changed: 1 Warnings: 0mysql>/*查詢?nèi)罩颈砜词欠窀碌臅r候有觸發(fā)寫入 */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類型觸發(fā)器：

mysql>/*這邊聲明SQL腳本的結(jié)束符為// */DELIMITER //DROP TRIGGER IF EXISTS trig_after_delete;CREATE TRIGGER trig_after_delete AFTER DELETE ON students FOR EACH ROWBEGIN 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 affectedmysql>/*重置SQL腳本的結(jié)束符為; */DELIMITER ;Query OK, 0 rows affectedmysql>/* 刪除數(shù)據(jù) */delete from students where studentid=21;Query OK, 1 row affectedmysql>/*查詢?nèi)罩究磩h除是否有觸發(fā)寫入*/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; --語法

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)建語句

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 ROWBEGIN 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; --語法

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的使用區(qū)別

觸發(fā)器針對的是數(shù)據(jù)庫中的每一行記錄，每行數(shù)據(jù)在操作前后都會有一個對應(yīng)的狀態(tài)，觸發(fā)器將沒有操作之前的狀態(tài)保存到 old 關(guān)鍵字中，將操作后的狀態(tài)保存到 new 中

new.cname --新增行（或修改前的行）的某列數(shù)據(jù) old.cname --刪除行（或修改后的行）的某列數(shù)據(jù)

需要注意的是，old 和 new 不是所有觸發(fā)器都有

觸發(fā)器類型 new和old的使用 INSERT型觸發(fā)器 沒有 old，只有 new，new 表示將要(插入前)或者已經(jīng)增加(插入后)的數(shù)據(jù) UPDATE型觸發(fā)器 既有 old 也有 new，old 表示更新之前的數(shù)據(jù)，new 表示更新之后的數(shù)據(jù) DELETE型觸發(fā)器 沒有 new，只有 old，old 表示將要(刪除前)或者已經(jīng)被刪除(刪除后)的數(shù)據(jù)

其實我們上面已經(jīng)用過 new/old 來創(chuàng)建觸發(fā)器并獲取數(shù)據(jù)，我們根據(jù)這個表格重新來修改下那個更新觸發(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 ROWBEGIN 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 affectedmysql>/*重置SQL腳本的結(jié)束符為; */DELIMITER ;Query OK, 0 rows affectedmysql>/*更新成績和名稱 */update students set score=106,studentname=’trigger2’ where studentid=17;Query OK, 1 row affectedRows matched: 1 Changed: 1 Warnings: 0mysql>/*根據(jù)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無法觸發(fā)對同表的修改

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

mysql>/*這邊聲明SQL腳本的結(jié)束符為// */DELIMITER //DROP TRIGGER IF EXISTS trig_after_insert;CREATE TRIGGER trig_after_insert AFTER INSERT ON students FOR EACH ROWBEGIN update students set score = score+1 where studentid= new.studentid;END //Query OK, 0 rows affectedmysql>/*重置SQL腳本的結(jié)束符為; */DELIMITER ;Query OK, 0 rows affectedmysql>/*插入一條數(shù)據(jù)之后因為觸發(fā)了對本表的修改，所以報錯了 */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ā)器可以通過數(shù)據(jù)庫中的關(guān)聯(lián)表實現(xiàn)級聯(lián)更改，即一張表數(shù)據(jù)的改變會影響其他表的數(shù)據(jù)，如數(shù)據(jù)修改，數(shù)據(jù)統(tǒng)計，數(shù)據(jù)復(fù)制等。2、可以保證數(shù)據(jù)安全，并進行安全校驗，限制用戶具有操作數(shù)據(jù)庫的某種權(quán)利。3、可以對復(fù)雜邏輯的實現(xiàn)進行數(shù)據(jù)完整性檢查和約束。4、使用觸發(fā)器的要應(yīng)需而用，如果過分依賴觸發(fā)器，會影響數(shù)據(jù)庫的結(jié)構(gòu)，增加數(shù)據(jù)庫的執(zhí)行和維護成本。5、觸發(fā)器有BEFORE觸發(fā)器和AFTER觸發(fā)器的卻別，所以它的執(zhí)行步驟是先執(zhí)行 BEFORE觸發(fā)器，再執(zhí)行業(yè)務(wù)腳本，最后執(zhí)行AFTER觸發(fā)器。需要注意的是 某一個步驟錯誤后就不再執(zhí)行了， 如果是事務(wù)表，會被回滾，如果是非事務(wù)表，那么就無法回滾了，數(shù)據(jù)可能有不一致性。

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

以上就是MySQL 觸發(fā)器的使用及需要注意的地方的詳細內(nèi)容，更多關(guān)于MySQL 觸發(fā)器的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！