怎么使用MySQL觸發(fā)器

發(fā)布時間：2020-07-23 16:11:56 來源：億速云閱讀：226 作者：小豬欄目：MySQL數(shù)據(jù)庫

這篇文章主要講解了怎么使用MySQL觸發(fā)器，內(nèi)容清晰明了，對此有興趣的小伙伴可以學(xué)習(xí)一下，相信大家閱讀完之后會有幫助。

一、MySQL觸發(fā)器創(chuàng)建：

１、MySQL觸發(fā)器的創(chuàng)建語法：

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)建語法中的關(guān)鍵詞解釋：

字段	含義	可能的值
DEFINER=	可選參數(shù)，指定創(chuàng)建者，默認(rèn)為當(dāng)前登錄用戶（CURRENT_USER）；該觸發(fā)器將以此參數(shù)指定的用戶執(zhí)行，所以需要考慮權(quán)限問題；	DEFINER='root@%' DEFINER=CURRENT_USER
trigger_name	觸發(fā)器名稱，最好由表名+觸發(fā)事件關(guān)鍵詞+觸發(fā)時間關(guān)鍵詞組成；
trigger_time	觸發(fā)時間，在某個事件之前還是之后；	BEFORE、AFTER
trigger_event	觸發(fā)事件，如插入時觸發(fā)、刪除時觸發(fā)；　　INSERT：插入操作觸發(fā)器，INSERT、LOAD DATA、REPLACE時觸發(fā)；　　UPDATE：更新操作觸發(fā)器，UPDATE操作時觸發(fā)；　　DELETE：刪除操作觸發(fā)器，DELETE、REPLACE操作時觸發(fā)；	INSERT、UPDATE、DELETE
table_name	觸發(fā)操作時間的表名；
trigger_order	可選參數(shù)，如果定義了多個具有相同觸發(fā)事件和觸法時間的觸發(fā)器時（如：BEFORE UPDATE），默認(rèn)觸發(fā)順序與觸發(fā)器的創(chuàng)建順序一致，可以使用此參數(shù)來改變它們觸發(fā)順序。mysql 5.7.2起開始支持此參數(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語句內(nèi)容，一般以begin開頭，end結(jié)尾	begin .. end

３、觸發(fā)執(zhí)行語句內(nèi)容（trigger_body）中的OLD，NEW：

　　在trigger_body中，我們可以使用NEW表示將要插入的新行（相當(dāng)于MS SQL的INSERTED），OLD表示將要刪除的舊行（相當(dāng)于MS SQL的DELETED）。通過OLD，NEW中獲取它們的字段內(nèi)容，方便在觸發(fā)操作中使用，下面是對應(yīng)事件是否支持OLD、NEW的對應(yīng)關(guān)系：

事件	OLD	NEW
INSERT	×	√
DELETE	√	×
UPDATE	√	√

　　由于UPDATE相當(dāng)于刪除舊行（OLD），然后插入新行（NEW），所以UPDATE同時支持OLD、NEW；

４、MySQL分隔符（DELIMITER）：

　　MySQL默認(rèn)使用“;”作為分隔符，SQL語句遇到“;”就會提交。而我們的觸發(fā)器中可能會有多個“;”符，為了防止觸發(fā)器創(chuàng)建語句過早的提交，我們需要臨時修改MySQL分隔符，創(chuàng)建完后，再將分隔符改回來。使用DELIMITER可以修改分隔符，如下：

DELIMITER $
... --觸發(fā)器創(chuàng)建語句；
$  --提交創(chuàng)建語句；
DELIMITER ;

二、MySQL觸發(fā)器創(chuàng)建進(jìn)階：

1、MySQL觸發(fā)器中使用變量：

　　MySQL觸發(fā)器中變量變量前面加'@'，無需定義，可以直接使用：

-- 變量直接賦值
set @num=999;
 
-- 使用select語句查詢出來的數(shù)據(jù)方式賦值，需要加括號：
set @name =(select name from table);

2、MySQL觸發(fā)器中使用if語做條件判斷：

-- 簡單的if語句：
set sex = if (new.sex=1, '男', '女');
 
-- 多條件if語句：
if old.type=1 then
  update table ...;
elseif old.type=2 then
  update table ...;
end if;

三、ＭｙSQL查看觸發(fā)器：

　　可以使用“show triggers;”查看觸發(fā)器。由于MySQL創(chuàng)建的觸發(fā)器保存在“information_schema庫中的triggers表中，所以還可以通過查詢此表查看觸發(fā)器：

-- 通過information_schema.triggers表查看觸發(fā)器：
select * from information_schema.triggers;
 
-- mysql 查看當(dāng)前數(shù)據(jù)庫的觸發(fā)器
show triggers;
 
-- mysql 查看指定數(shù)據(jù)庫"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ā)更新同一張表：

　　下面我們有一個表“tmp1”，tmp1表有兩個整型字段：n1、n2。我們要通過觸發(fā)器實現(xiàn)，在tmp插入記錄時，自動將n2字段的值設(shè)置為n1字段的5倍。

　創(chuàng)建測試表和觸發(fā)器：

-- 創(chuàng)建測試表
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 ;

測試觸發(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)

２、MySQL觸發(fā)器Update觸發(fā)更新另一張表：

　　下面有有兩個表tmp1、tmp2，兩個表都有一個相同的字段name。使用觸發(fā)器實現(xiàn)更新一個表的name時，將另外一個表的name也更新。

　創(chuàng)建測試表和觸發(fā)器：

-- 創(chuàng)建測試表和插入測試數(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, '愛E族');
insert into tmp2 values(1, '愛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 ;

測試觸發(fā)更新效果：

mysql> select * from tmp1;
+------+---------+
| id  | name  |
+------+---------+
|  1 | 愛E族  |
+------+---------+
1 row in set (0.00 sec)
 
mysql> select * from tmp2;
+------+---------+
| fid | name  |
+------+---------+
|  1 | 愛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)

看完上述內(nèi)容，是不是對怎么使用MySQL觸發(fā)器有進(jìn)一步的了解，如果還想學(xué)習(xí)更多內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道。

向AI問一下細(xì)節(jié)

怎么使用MySQL觸發(fā)器

１、MySQL觸發(fā)器的創(chuàng)建語法：

2、MySQL創(chuàng)建語法中的關(guān)鍵詞解釋：

３、觸發(fā)執(zhí)行語句內(nèi)容（trigger_body）中的OLD，NEW：

４、MySQL分隔符（DELIMITER）：

二、MySQL觸發(fā)器創(chuàng)建進(jìn)階：

1、MySQL觸發(fā)器中使用變量：

2、MySQL觸發(fā)器中使用if語做條件判斷：

三、ＭｙSQL查看觸發(fā)器：

四、MySQL刪除觸發(fā)器：

1、可以使用drop trigger刪除觸發(fā)器：

2、刪除前先判斷觸發(fā)器是否存在：

五、Msql觸發(fā)器用法舉例：

1、MySQL觸發(fā)器Insert觸發(fā)更新同一張表：

２、MySQL觸發(fā)器Update觸發(fā)更新另一張表：

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽