MySQL觸發(fā)器基本用法詳解【創(chuàng)建、查看、刪除等】
本文實(shí)例講述了MySQL觸發(fā)器基本用法。分享給大家供大家參考,具體如下:
一、MySQL觸發(fā)器創(chuàng)建:
1、MySQL觸發(fā)器的創(chuàng)建語(yǔ)法:
CREATE [DEFINER = { 'user' | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body
2、MySQL創(chuàng)建語(yǔ)法中的關(guān)鍵詞解釋:
| 字段 | 含義 | 可能的值 |
|---|---|---|
| DEFINER= | 可選參數(shù),指定創(chuàng)建者,默認(rèn)為當(dāng)前登錄用戶(CURRENT_USER); 該觸發(fā)器將以此參數(shù)指定的用戶執(zhí)行,所以需要考慮權(quán)限問(wèn)題; |
DEFINER='root@%' DEFINER=CURRENT_USER |
| trigger_name | 觸發(fā)器名稱,最好由表名+觸發(fā)事件關(guān)鍵詞+觸發(fā)時(shí)間關(guān)鍵詞組成; | |
| trigger_time | 觸發(fā)時(shí)間,在某個(gè)事件之前還是之后; | BEFORE、AFTER |
| trigger_event | 觸發(fā)事件,如插入時(shí)觸發(fā)、刪除時(shí)觸發(fā); INSERT:插入操作觸發(fā)器,INSERT、LOAD DATA、REPLACE時(shí)觸發(fā); UPDATE:更新操作觸發(fā)器,UPDATE操作時(shí)觸發(fā); DELETE:刪除操作觸發(fā)器,DELETE、REPLACE操作時(shí)觸發(fā); |
INSERT、UPDATE、DELETE |
| table_name | 觸發(fā)操作時(shí)間的表名; | |
| trigger_order | 可選參數(shù),如果定義了多個(gè)具有相同觸發(fā)事件和觸法時(shí)間的觸發(fā)器時(shí)( 如:BEFORE UPDATE),默認(rèn)觸發(fā)順序與觸發(fā)器的創(chuàng)建順序一致,可以 使用此參數(shù)來(lái)改變它們觸發(fā)順序。mysql 5.7.2起開(kāi)始支持此參數(shù)。 FOLLOWS:當(dāng)前創(chuàng)建觸發(fā)器在現(xiàn)有觸發(fā)器之后激活; PRECEDES:當(dāng)前創(chuàng)建觸發(fā)器在現(xiàn)有觸發(fā)器之前激活; |
FOLLOWS、PRECEDES |
| trigger_body | 觸發(fā)執(zhí)行的SQL語(yǔ)句內(nèi)容,一般以begin開(kāi)頭,end結(jié)尾 | begin .. end |
3、觸發(fā)執(zhí)行語(yǔ)句內(nèi)容(trigger_body)中的OLD,NEW:
在trigger_body中,我們可以使用NEW表示將要插入的新行(相當(dāng)于MS SQL的INSERTED),OLD表示將要?jiǎng)h除的舊行(相當(dāng)于MS SQL的DELETED)。通過(guò)OLD,NEW中獲取它們的字段內(nèi)容,方便在觸發(fā)操作中使用,下面是對(duì)應(yīng)事件是否支持OLD、NEW的對(duì)應(yīng)關(guān)系:
| 事件 | OLD | NEW |
|---|---|---|
| INSERT | × | √ |
| DELETE | √ | × |
| UPDATE | √ | √ |
由于UPDATE相當(dāng)于刪除舊行(OLD),然后插入新行(NEW),所以UPDATE同時(shí)支持OLD、NEW;
4、MySQL分隔符(DELIMITER):
MySQL默認(rèn)使用“;”作為分隔符,SQL語(yǔ)句遇到“;”就會(huì)提交。而我們的觸發(fā)器中可能會(huì)有多個(gè)“;”符,為了防止觸發(fā)器創(chuàng)建語(yǔ)句過(guò)早的提交,我們需要臨時(shí)修改MySQL分隔符,創(chuàng)建完后,再將分隔符改回來(lái)。使用DELIMITER可以修改分隔符,如下:
DELIMITER $ ... --觸發(fā)器創(chuàng)建語(yǔ)句; $ --提交創(chuàng)建語(yǔ)句; DELIMITER ;
二、MySQL觸發(fā)器創(chuàng)建進(jìn)階:
1、MySQL觸發(fā)器中使用變量:
MySQL觸發(fā)器中變量變量前面加'@',無(wú)需定義,可以直接使用:
-- 變量直接賦值 set @num=999; -- 使用select語(yǔ)句查詢出來(lái)的數(shù)據(jù)方式賦值,需要加括號(hào): set @name =(select name from table);
2、MySQL觸發(fā)器中使用if語(yǔ)做條件判斷:
-- 簡(jiǎn)單的if語(yǔ)句: set sex = if (new.sex=1, '男', '女'); -- 多條件if語(yǔ)句: if old.type=1 then update table ...; elseif old.type=2 then update table ...; end if;
三、MySQL查看觸發(fā)器:
可以使用“show triggers;”查看觸發(fā)器。由于MySQL創(chuàng)建的觸發(fā)器保存在“information_schema庫(kù)中的triggers表中,所以還可以通過(guò)查詢此表查看觸發(fā)器:
-- 通過(guò)information_schema.triggers表查看觸發(fā)器: select * from information_schema.triggers; -- mysql 查看當(dāng)前數(shù)據(jù)庫(kù)的觸發(fā)器 show triggers; -- mysql 查看指定數(shù)據(jù)庫(kù)"aiezu"的觸發(fā)器 show triggers from aiezu;
四、MySQL刪除觸發(fā)器:
1、可以使用drop trigger刪除觸發(fā)器:
drop trigger trigger_name;
2、刪除前先判斷觸發(fā)器是否存在:
drop trigger if exists trigger_name
五、Msql觸發(fā)器用法舉例:
1、MySQL觸發(fā)器Insert觸發(fā)更新同一張表:
下面我們有一個(gè)表“tmp1”,tmp1表有兩個(gè)整型字段:n1、n2。我們要通過(guò)觸發(fā)器實(shí)現(xiàn),在tmp插入記錄時(shí),自動(dòng)將n2字段的值設(shè)置為n1字段的5倍。
創(chuàng)建測(cè)試表和觸發(fā)器:
-- 創(chuàng)建測(cè)試表 drop table if exists tmp1; create table tmp1 (n1 int, n2 int); -- 創(chuàng)建觸發(fā)器 DELIMITER $ drop trigger if exists tmp1_insert$ create trigger tmp1_insert before insert on tmp1 for each row begin set new.n2 = new.n1*5; end$ DELIMITER ;
測(cè)試觸發(fā)更新效果:
mysql> insert tmp1(n1) values(18); Query OK, 1 row affected (0.01 sec) mysql> insert tmp1(n1) values(99); Query OK, 1 row affected (0.00 sec) mysql> select * from tmp1; +------+------+ | n1 | n2 | +------+------+ | 18 | 90 | | 99 | 495 | +------+------+ 2 rows in set (0.00 sec)
2、MySQL觸發(fā)器Update觸發(fā)更新另一張表:
下面有有兩個(gè)表tmp1、tmp2,兩個(gè)表都有一個(gè)相同的字段name。使用觸發(fā)器實(shí)現(xiàn)更新一個(gè)表的name時(shí),將另外一個(gè)表的name也更新。
創(chuàng)建測(cè)試表和觸發(fā)器:
-- 創(chuàng)建測(cè)試表和插入測(cè)試數(shù)據(jù) drop table if exists tmp1; drop table if exists tmp2; create table tmp1 (id int, name varchar(128)) default charset='utf8'; create table tmp2 (fid int, name varchar(128)) default charset='utf8'; insert into tmp1 values(1, '愛(ài)E族'); insert into tmp2 values(1, '愛(ài)E族'); -- 創(chuàng)建觸發(fā)器 DELIMITER $ drop trigger if exists tmp1_update$ create trigger tmp1_update after update on tmp1 for each row begin update tmp2 set name=new.name where fid=new.id; end$ DELIMITER ;
測(cè)試觸發(fā)更新效果:
mysql> select * from tmp1; +------+---------+ | id | name | +------+---------+ | 1 | 愛(ài)E族 | +------+---------+ 1 row in set (0.00 sec) mysql> select * from tmp2; +------+---------+ | fid | name | +------+---------+ | 1 | 愛(ài)E族 | +------+---------+ 1 row in set (0.00 sec) mysql> update tmp1 set name='aiezu.com' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tmp1; +------+-----------+ | id | name | +------+-----------+ | 1 | aiezu.com | +------+-----------+ 1 row in set (0.00 sec) mysql> select * from tmp2; +------+-----------+ | fid | name | +------+-----------+ | 1 | aiezu.com | +------+-----------+ 1 row in set (0.00 sec)
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《MySQL查詢技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》及《MySQL常用函數(shù)大匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。
- mysql創(chuàng)建觸發(fā)器時(shí)報(bào)1064錯(cuò)誤問(wèn)題及解決
- 詳解MySQL中存儲(chǔ)函數(shù)創(chuàng)建與觸發(fā)器設(shè)置
- mysql 觸發(fā)器創(chuàng)建與使用方法示例
- mysql觸發(fā)器之創(chuàng)建多個(gè)觸發(fā)器操作實(shí)例分析
- mysql觸發(fā)器之創(chuàng)建使用觸發(fā)器簡(jiǎn)單示例
- mysql觸發(fā)器簡(jiǎn)介、創(chuàng)建觸發(fā)器及使用限制分析
- MySQL如何創(chuàng)建觸發(fā)器(CREATE TRIGGER)
相關(guān)文章
Linux中更改轉(zhuǎn)移mysql數(shù)據(jù)庫(kù)目錄的步驟
前幾天發(fā)現(xiàn)由于MySQL的數(shù)據(jù)庫(kù)太大,默認(rèn)安裝的/var盤已經(jīng)再也無(wú)法容納新增加的數(shù)據(jù),只能想辦法轉(zhuǎn)移數(shù)據(jù)的目錄。網(wǎng)上有很多相關(guān)的文章寫到轉(zhuǎn)移數(shù)據(jù)庫(kù)目錄的文章,但轉(zhuǎn)載的過(guò)程中還會(huì)有一些錯(cuò)誤,因?yàn)榇蟛糠秩烁揪蜎](méi)測(cè)試過(guò),這篇文章是本文測(cè)試過(guò)整理好后分享給大家。2016-11-11
深入mysql YEAR() MONTH() DAYOFMONTH()日期函數(shù)的詳解
本篇文章是對(duì)mysql中的YEAR() MONTH() DAYOFMONTH()日期函數(shù)進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06
mysql 無(wú)法連接問(wèn)題的定位和修復(fù)過(guò)程分享
開(kāi)發(fā)的一款網(wǎng)站防護(hù)產(chǎn)品中出現(xiàn)了一個(gè)客戶端上安裝后Mysql每隔一段時(shí)間就出現(xiàn)問(wèn)題,這個(gè)問(wèn)題是客戶反饋的,所以需要去復(fù)現(xiàn)和定位2013-03-03
MySQL創(chuàng)建索引/判斷索引是否生效的問(wèn)題
這篇文章主要介紹了MySQL創(chuàng)建索引/判斷索引是否生效的問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08
mysql 5.7.21 解壓版通過(guò)歷史data目錄恢復(fù)數(shù)據(jù)的教程圖解
本文通過(guò)圖文并茂的形式給大家介紹了mysql 5.7.21 解壓版,通過(guò)歷史data目錄恢復(fù)數(shù)據(jù)的方法,非常不錯(cuò),具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2018-09-09
數(shù)據(jù)庫(kù)SQL SELECT查詢的工作原理
今天小編就為大家分享一篇關(guān)于數(shù)據(jù)庫(kù)SQL SELECT查詢的工作原理,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧2019-03-03

