MySQL觸發器trigger的使用
一、觸發器簡介
1、什麼是觸發器?
觸發器是與表有關的數據庫對象,在滿足定義條件時觸發,並執行觸發器中定義的語句集合。
2、觸發器的特性
- 有
begin end
體,begin end
;之間的語句可以寫的簡單或者復雜 - 什麼條件會觸發:I、D、U
- 什麼時候觸發:在增刪改前或者後
- 觸發頻率:針對每一行執行
- 觸發器定義在表上,附著在表上。
也就是由事件來觸發某個操作,事件包括INSERT
語句,UPDATE
語句和DELETE
語句;可以協助應用在數據庫端確保數據的完整性。
註意:cannot associate a trigger with a TEMPORARY table or a view.
!!盡量少使用觸發器,不建議使用。
假設觸發器觸發每次執行1s,insert table 500
條數據,那麼就需要觸發500次觸發器,光是觸發器執行的時間就花費瞭500s
,而insert 500
條數據一共是1s,那麼這個insert
的效率就非常低瞭。因此我們特別需要註意的一點是觸發器的begin end
;之間的語句的執行效率一定要高,資源消耗要小。
觸發器盡量少的使用,因為不管如何,它還是很消耗資源,如果使用的話要謹慎的使用,確定它是非常高效的:觸發器是針對每一行的;對增刪改非常頻繁的表上切記不要使用觸發器,因為它會非常消耗資源。
二、、創建觸發器
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
> BEFORE
和AFTER
參數指定瞭觸發執行的時間,在事件之前或是之後。
> FOR EACH ROW
表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器,也就是說觸發器的觸發頻率是針對每一行數據觸發一次。
> tigger_event
詳解:
①INSERT型觸發器
:插入某一行時激活觸發器,可能通過INSERT
、LOAD DATA
、REPLACE
語句觸發(LOAD DAT
語句用於將一個文件裝入到一個數據表中,相當與一系列的INSERT操作);
②UPDATE型觸發器
:更改某一行時激活觸發器,可能通過UPDATE
語句觸發;
③DELETE型觸發器
:刪除某一行時激活觸發器,可能通過DELETE
、REPLACE
語句觸發。
> trigger_order
是MySQL5.7
之後的一個功能,用於定義多個觸發器,使用follows
(尾隨)或precedes
(在…之先)來選擇觸發器執行的先後順序。
1、創建隻有一個執行語句的觸發器
CREATE TRIGGER
觸發器名BEFORE|AFTER
觸發事件ON
表名FOR EACH ROW
執行語句;
例1:創建瞭一個名為trig1的觸發器,一旦在work表中有插入動作,就會自動往time表裡插入當前時間
mysql> CREATE TRIGGER trig1 AFTER INSERT -> ON work FOR EACH ROW -> INSERT INTO time VALUES(NOW());
2、創建有多個執行語句的觸發器
CREATE TRIGGER
觸發器名 BEFORE|AFTER
觸發事件
ON 表名 FOR EACH ROW
BEGIN
執行語句列表
END;
例2:定義一個觸發器,一旦有滿足條件的刪除操作,就會執行BEGIN
和END
中的語句
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
,用來表示觸發器的所在表中,觸發瞭觸發器的那一行數據,來引用觸發器中發生變化的記錄內容,具體地:
①在INSERT
型觸發器中,NEW用來表示將要(BEFORE
)或已經(AFTER
)插入的新數據;
②在UPDATE
型觸發器中,OLD
用來表示將要或已經被修改的原數據,NEW
用來表示將要或已經修改為的新數據;
③在DELETE
型觸發器中,OLD
用來表示將要或已經被刪除的原數據;
使用方法:
NEW.columnName (columnName為相應數據表某一列名)
另外,OLD
是隻讀的,而NEW
則可以在觸發器中使用 SET
賦值,這樣不會再次觸發觸發器,造成循環調用(如每插入一個學生前,都在其學號前加“2013”)。
例3:
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 | +----------+---------+
三、查看觸發器
1、SHOW TRIGGERS語句查看觸發器信息
mysql> SHOW TRIGGERS\G;
……
結果,顯示所有觸發器的基本信息;無法查詢指定的觸發器。
2、在information_schema.triggers表中查看觸發器信息
mysql> SELECT * FROM information_schema.triggers\G
……
結果,顯示所有觸發器的詳細信息;同時,該方法可以查詢制定觸發器的詳細信息。
mysql> select * from information_schema.triggers -> where trigger_name='upd_check'\G; Tips:
所有觸發器信息都存儲在information_schema
數據庫下的triggers
表中,可以使用SELECT
語句查詢,如果觸發器信息過多,最好通過TRIGGER_NAME
字段指定查詢。
四、刪除觸發器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
刪除觸發器之後最好使用上面的方法查看一遍;同時,也可以使用database.trig
來指定某個數據庫中的觸發器。
Tips:
如果不需要某個觸發器時一定要將這個觸發器刪除,以免造成意外操作,這很關鍵。
到此這篇關於MySQL
觸發器trigger
的使用的文章就介紹到這瞭,更多相關MySQL
觸發器trigger
使用內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!