一、觸發(fā)器簡介
1、什么是觸發(fā)器？
觸發(fā)器是與表有關的數(shù)據(jù)庫對象，在滿足定義條件時觸發(fā)，并執(zhí)行觸發(fā)器中定義的語句集合。
2、觸發(fā)器的特性
-
有
begin end體，begin end;之間的語句可以寫的簡單或者復雜 - 什么條件會觸發(fā)：I、D、U
- 什么時候觸發(fā)：在增刪改前或者后
- 觸發(fā)頻率：針對每一行執(zhí)行
- 觸發(fā)器定義在表上，附著在表上。
也就是由事件來觸發(fā)某個操作，事件包括INSERT語句，UPDATE語句和DELETE語句；可以協(xié)助應用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性。
注意：cannot associate a trigger with a TEMPORARY table or a view.
！！盡量少使用觸發(fā)器，不建議使用。
假設觸發(fā)器觸發(fā)每次執(zhí)行1s，insert table 500條數(shù)據(jù)，那么就需要觸發(fā)500次觸發(fā)器，光是觸發(fā)器執(zhí)行的時間就花費了500s，而insert 500條數(shù)據(jù)一共是1s，那么這個insert的效率就非常低了。因此我們特別需要注意的一點是觸發(fā)器的begin end;之間的語句的執(zhí)行效率一定要高，資源消耗要小。
觸發(fā)器盡量少的使用，因為不管如何，它還是很消耗資源，如果使用的話要謹慎的使用，確定它是非常高效的：觸發(fā)器是針對每一行的；對增刪改非常頻繁的表上切記不要使用觸發(fā)器，因為它會非常消耗資源。
二、、創(chuàng)建觸發(fā)器
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE [DEFINER = { user | CURRENT_USER }]TRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROW [trigger_order]trigger_bodytrigger_time: { BEFORE | AFTER }trigger_event: { INSERT | UPDATE | DELETE }trigger_order: { FOLLOWS | PRECEDES } other_trigger_name |
> BEFORE和AFTER參數(shù)指定了觸發(fā)執(zhí)行的時間，在事件之前或是之后。
> FOR EACH ROW表示任何一條記錄上的操作滿足觸發(fā)事件都會觸發(fā)該觸發(fā)器，也就是說觸發(fā)器的觸發(fā)頻率是針對每一行數(shù)據(jù)觸發(fā)一次。
> tigger_event詳解：
①INSERT型觸發(fā)器：插入某一行時激活觸發(fā)器，可能通過INSERT、LOAD DATA、REPLACE 語句觸發(fā)(LOAD DAT語句用于將一個文件裝入到一個數(shù)據(jù)表中，相當與一系列的INSERT操作)；
②UPDATE型觸發(fā)器：更改某一行時激活觸發(fā)器，可能通過UPDATE語句觸發(fā)；
③DELETE型觸發(fā)器：刪除某一行時激活觸發(fā)器，可能通過DELETE、REPLACE語句觸發(fā)。
> trigger_order是MySQL5.7之后的一個功能，用于定義多個觸發(fā)器，使用follows(尾隨)或precedes(在…之先)來選擇觸發(fā)器執(zhí)行的先后順序。
1、創(chuàng)建只有一個執(zhí)行語句的觸發(fā)器
CREATE TRIGGER觸發(fā)器名BEFORE|AFTER觸發(fā)事件ON表名FOR EACH ROW執(zhí)行語句;
例1：創(chuàng)建了一個名為trig1的觸發(fā)器，一旦在work表中有插入動作，就會自動往time表里插入當前時間
|
1
2
3
4
|
mysql> CREATE TRIGGER trig1 AFTER INSERT -> ON work FOR EACH ROW -> INSERT INTO time VALUES(NOW()); |
2、創(chuàng)建有多個執(zhí)行語句的觸發(fā)器
CREATE TRIGGER 觸發(fā)器名 BEFORE|AFTER 觸發(fā)事件
ON 表名 FOR EACH ROW
BEGIN
執(zhí)行語句列表
END;
例2：定義一個觸發(fā)器，一旦有滿足條件的刪除操作，就會執(zhí)行BEGIN和END中的語句
|
1
2
3
4
5
6
7
8
|
mysql> DELIMITER ||mysql> CREATE TRIGGER trig2 BEFORE DELETE -> ON work FOR EACH ROW -> BEGIN -> INSERT INTO time VALUES(NOW()); -> INSERT INTO time VALUES(NOW()); -> END||mysql> DELIMITER ; |
3、NEW與OLD詳解
MySQL 中定義了 NEW 和 OLD，用來表示觸發(fā)器的所在表中，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)，來引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容，具體地：
①在INSERT型觸發(fā)器中，NEW用來表示將要（BEFORE）或已經(jīng)（AFTER）插入的新數(shù)據(jù)；
②在UPDATE型觸發(fā)器中，OLD用來表示將要或已經(jīng)被修改的原數(shù)據(jù)，NEW用來表示將要或已經(jīng)修改為的新數(shù)據(jù)；
③在DELETE型觸發(fā)器中，OLD用來表示將要或已經(jīng)被刪除的原數(shù)據(jù)；
使用方法：
NEW.columnName （columnName為相應數(shù)據(jù)表某一列名）
另外，OLD是只讀的，而NEW則可以在觸發(fā)器中使用 SET 賦值，這樣不會再次觸發(fā)觸發(fā)器，造成循環(huán)調(diào)用（如每插入一個學生前，都在其學號前加“2013”）。
例3：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);mysql> delimiter $$mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account -> FOR EACH ROW -> BEGIN -> IF NEW.amount < 0 THEN -> SET NEW.amount = 0; -> ELSEIF NEW.amount > 100 THEN -> SET NEW.amount = 100; -> END IF; -> END$$mysql> delimiter ;mysql> update account set amount=-10 where acct_num=137;mysql> select * from account;+----------+---------+| acct_num | amount |+----------+---------+| 137 | 0.00 || 141 | 1937.50 || 97 | -100.00 |+----------+---------+mysql> update account set amount=200 where acct_num=137;mysql> select * from account;+----------+---------+| acct_num | amount |+----------+---------+| 137 | 100.00 || 141 | 1937.50 || 97 | -100.00 |+----------+---------+ |
三、查看觸發(fā)器
1、SHOW TRIGGERS語句查看觸發(fā)器信息
mysql> SHOW TRIGGERS\G;
……
結(jié)果，顯示所有觸發(fā)器的基本信息；無法查詢指定的觸發(fā)器。
2、在information_schema.triggers表中查看觸發(fā)器信息
mysql> SELECT * FROM information_schema.triggers\G
……
結(jié)果，顯示所有觸發(fā)器的詳細信息；同時，該方法可以查詢制定觸發(fā)器的詳細信息。
|
1
2
3
|
mysql> select * from information_schema.triggers -> where trigger_name='upd_check'\G;Tips： |
所有觸發(fā)器信息都存儲在information_schema數(shù)據(jù)庫下的triggers表中，可以使用SELECT語句查詢，如果觸發(fā)器信息過多，最好通過TRIGGER_NAME字段指定查詢。
四、刪除觸發(fā)器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
刪除觸發(fā)器之后最好使用上面的方法查看一遍；同時，也可以使用database.trig來指定某個數(shù)據(jù)庫中的觸發(fā)器。
Tips：
如果不需要某個觸發(fā)器時一定要將這個觸發(fā)器刪除，以免造成意外操作，這很關鍵。
到此這篇關于MySQL觸發(fā)器trigger的使用的文章就介紹到這了,更多相關MySQL觸發(fā)器trigger使用內(nèi)容請搜索服務器之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://www.cnblogs.com/geaozhang/p/6819648.html
