溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器

發(fā)布時(shí)間:2020-08-03 16:27:55 來(lái)源:網(wǎng)絡(luò) 閱讀:23484 作者:天山老妖S 欄目:MySQL數(shù)據(jù)庫(kù)

MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器

一、觸發(fā)器簡(jiǎn)介

1、觸發(fā)器簡(jiǎn)介

觸發(fā)器是和表關(guān)聯(lián)的特殊的存儲(chǔ)過(guò)程,可以在插入,刪除或修改表中的數(shù)據(jù)時(shí)觸發(fā)執(zhí)行,比數(shù)據(jù)庫(kù)本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。

2、觸發(fā)器的優(yōu)點(diǎn)

A、安全性
可以基于數(shù)據(jù)庫(kù)的值使用戶(hù)具有操作數(shù)據(jù)庫(kù)的某種權(quán)利??梢曰跁r(shí)間限制用戶(hù)的操作,例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫(kù)數(shù)據(jù);可以基于數(shù)據(jù)庫(kù)中的數(shù)據(jù)限制用戶(hù)的操作,例如不允許股票的價(jià)格的升幅一次超過(guò)10%。
B、審計(jì)
可以跟蹤用戶(hù)對(duì)數(shù)據(jù)庫(kù)的操作。???審計(jì)用戶(hù)操作數(shù)據(jù)庫(kù)的語(yǔ)句;把用戶(hù)對(duì)數(shù)據(jù)庫(kù)的更新寫(xiě)入審計(jì)表。
C、實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則
實(shí)現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制。與規(guī)則不同,觸發(fā)器可以引用列或數(shù)據(jù)庫(kù)對(duì)象。例如,觸發(fā)器可回退任何企圖吃進(jìn)超過(guò)自己保證金的期貨。提供可變的缺省值。
D、實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)相關(guān)完整性規(guī)則。
觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中相關(guān)的表進(jìn)行連環(huán)更新。
??在修改或刪除時(shí)級(jí)聯(lián)修改或刪除其它表中的與之匹配的行。
??在修改或刪除時(shí)把其它表中的與之匹配的行設(shè)成NULL值。
在修改或刪除時(shí)把其它表中的與之匹配的行級(jí)聯(lián)設(shè)成缺省值。
??觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化,取消試圖進(jìn)行數(shù)據(jù)更新的事務(wù)。
E、同步實(shí)時(shí)地復(fù)制表中的數(shù)據(jù)。
F、SQL觸發(fā)器提供了運(yùn)行計(jì)劃任務(wù)的另一種方法。自動(dòng)計(jì)算數(shù)據(jù)值,如果數(shù)據(jù)的值達(dá)到了一定的要求,則進(jìn)行特定的處理。例如,如果公司的帳號(hào)上的資金低于5萬(wàn)元?jiǎng)t立即給財(cái)務(wù)人員發(fā)送警告數(shù)據(jù)。

3、觸發(fā)器的限制

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

二、觸發(fā)器的使用

1、創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器的語(yǔ)法:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name:觸發(fā)器的名稱(chēng)。
tirgger_time:觸發(fā)時(shí)機(jī),為BEFORE或者AFTER。
trigger_event:觸發(fā)事件,為INSERT、DELETE或者UPDATE。
tb_name:表示建立觸發(fā)器的表名,在哪張表上建立觸發(fā)器。
trigger_stmt:觸發(fā)器的程序體,可以是一條SQL語(yǔ)句或者是用BEGIN和END包含的多條語(yǔ)句。
FOR EACH ROW表示任何一條記錄上的操作滿(mǎn)足觸發(fā)事件都會(huì)觸發(fā)該觸發(fā)器。
MySQL除了對(duì)INSERT、UPDATE、DELETE基本操作進(jìn)行定義外,還定義了LOAD DATA和REPLACE語(yǔ)句,這兩種語(yǔ)句也能引起上述6中類(lèi)型的觸發(fā)器的觸發(fā)。
LOAD DATA 語(yǔ)句用于將一個(gè)文件裝入到一個(gè)數(shù)據(jù)表中,相當(dāng)與一系列的 INSERT操作。
REPLACE語(yǔ)句一般來(lái)說(shuō)和INSERT語(yǔ)句很像,只是在表中有primary key或 unique索引時(shí),如果插入的數(shù)據(jù)和原來(lái)primary key或unique索引一致時(shí),會(huì)先刪除原來(lái)的數(shù)據(jù),然后增加一條新數(shù)據(jù)。
INSERT型觸發(fā)器:插入某一行時(shí)激活觸發(fā)器,通過(guò) INSERT、LOAD DATA、REPLACE語(yǔ)句觸發(fā);
UPDATE型觸發(fā)器:更改某一行時(shí)激活觸發(fā)器,通過(guò)UPDATE語(yǔ)句觸發(fā);
DELETE型觸發(fā)器:刪除某一行時(shí)激活觸發(fā)器,通過(guò)DELETE、REPLACE語(yǔ)句觸發(fā)。
變量聲明:
DECLARE var_name[,...] type [DEFAULT value]
對(duì)變量賦值采用SET 語(yǔ)句,語(yǔ)法為:
SET var_name = expr [,var_name = expr] ...
MySQL中定義了NEW和OLD,用來(lái)表示觸發(fā)器的所在表中,觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)。
在INSERT型觸發(fā)器中,NEW用來(lái)表示將要(BEFORE)或已經(jīng)(AFTER)插入的新數(shù)據(jù);
在UPDATE型觸發(fā)器中,OLD用來(lái)表示將要或已經(jīng)被修改的原數(shù)據(jù),NEW用來(lái)表示將要或已經(jīng)修改為的新數(shù)據(jù);
在DELETE型觸發(fā)器中,OLD用來(lái)表示將要或已經(jīng)被刪除的原數(shù)據(jù);
使用方法:NEW.columnName(columnName為相應(yīng)數(shù)據(jù)表某一列名)
另外,OLD是只讀的,而NEW則可以在觸發(fā)器中使用SET賦值,不會(huì)再次觸發(fā)觸發(fā)器,造成循環(huán)調(diào)用。

2、刪除觸發(fā)器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
schema_name是數(shù)據(jù)庫(kù)的名稱(chēng),是可選的。如果省略了schema,將從當(dāng)前數(shù)據(jù)庫(kù)中舍棄觸發(fā)程序。trigger_name是要?jiǎng)h除的觸發(fā)器的名稱(chēng)。

3、觸發(fā)器信息查看

在MySQL中,所有的觸發(fā)器的定義都存在于INFORMATION_SCHEMA數(shù)據(jù)庫(kù)的triggers表中,可以通過(guò)查詢(xún)命令SELECT來(lái)查看,具體語(yǔ)法如下:
SHOW TRIGGERS [FROM schema_name];
MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器
觸發(fā)器的執(zhí)行順序
InnoDB數(shù)據(jù)庫(kù),若SQL語(yǔ)句或觸發(fā)器執(zhí)行失敗,MySQL會(huì)回滾事務(wù),有:
A、如果BEFORE觸發(fā)器執(zhí)行失敗,SQL無(wú)法正確執(zhí)行。
B、SQL執(zhí)行失敗時(shí),AFTER型觸發(fā)器不會(huì)觸發(fā)。
C、AFTER類(lèi)型的觸發(fā)器執(zhí)行失敗,SQL會(huì)回滾。
MySQL的觸發(fā)器是按照BEFORE觸發(fā)器、行操作、AFTER觸發(fā)器的順序執(zhí)行的,其中任何一步發(fā)生錯(cuò)誤都不會(huì)繼續(xù)執(zhí)行剩下的操作,如果對(duì)事務(wù)表進(jìn)行的操作,如果出現(xiàn)錯(cuò)誤,那么將會(huì)被回滾,如果對(duì)非事務(wù)表進(jìn)行操作,那么就無(wú)法回滾,數(shù)據(jù)可能會(huì)出錯(cuò)。

三、觸發(fā)器應(yīng)用

1、實(shí)現(xiàn)業(yè)務(wù)邏輯

客戶(hù)下訂單訂購(gòu)商品,商品表自動(dòng)減少數(shù)量。
在商品表創(chuàng)建刪除觸發(fā)器,刪除某商品,自動(dòng)刪除該商品的訂單。
創(chuàng)建產(chǎn)品表,有產(chǎn)品編號(hào),產(chǎn)品名稱(chēng)、產(chǎn)品數(shù)量和產(chǎn)品價(jià)格四列,其中產(chǎn)品編號(hào)自增長(zhǎng)列,并設(shè)置成主鍵。

create table product
(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10),
price DOUBLE,
pnum INT
)ENGINE=innoDB default CHARSET=utf8;

創(chuàng)建訂單表,有三列,訂單編號(hào)、產(chǎn)品編號(hào)和數(shù)量,其中訂單編號(hào)自增長(zhǎng)列,并設(shè)置成主鍵。

create table orders
(
oid INT PRIMARY KEY AUTO_INCREMENT,
pid INT,
onum INT
)ENGINE=innoDB DEFAULT CHARSET=utf8;

插入三種產(chǎn)品,產(chǎn)品名稱(chēng)和數(shù)量以及價(jià)格。

insert into product(pname, pnum, price)values('桃子', 100, 2);
insert into product(pname, pnum, price)values('蘋(píng)果', 80, 8);
insert into product(pname, pnum, price)values('香蕉', 50, 5);

MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器
在訂單表上創(chuàng)建觸發(fā)器,當(dāng)有訂單,會(huì)根據(jù)訂單的產(chǎn)品編號(hào)和數(shù)量自動(dòng)減少產(chǎn)品的數(shù)量。觸發(fā)器中NEW代表一個(gè)表,存放插入的訂單記錄。

create trigger trigger_order
AFTER INSERT ON orders FOR EACH ROW
BEGIN
UPDATE product SET pnum=pnum-NEW.onum where pid = NEW.pid;
END

插入兩個(gè)訂單

INSERT INTO orders(pid, onum)VALUES(1, 10);
INSERT INTO orders(pid, onum)VALUES(2, 5);

查看產(chǎn)品表,可以看到對(duì)應(yīng)的產(chǎn)品數(shù)量減少。操作由訂單表的Insert觸發(fā)器完成。
MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器
在訂單表上創(chuàng)建新的觸發(fā)器,當(dāng)訂單定的某產(chǎn)品產(chǎn)品數(shù)量大于產(chǎn)品庫(kù)存,禁止下訂單,也就是禁止在訂單表中插入記錄。
一張表中只能有一個(gè)INSERT類(lèi)型的觸發(fā)器,先刪除INSERT觸發(fā)器。
drop trigger trigger_order;
MySQL不能在觸發(fā)器中通過(guò)回滾事務(wù)取消操作,但如果觸發(fā)器的SQL語(yǔ)句執(zhí)行過(guò)程中出現(xiàn)錯(cuò)誤,會(huì)自動(dòng)撤銷(xiāo)操作,曲線(xiàn)實(shí)現(xiàn)事務(wù)回滾。

create trigger trigger_order
BEFORE INSERT ON orders FOR EACH ROW
BEGIN
DECLARE var int;
DECLARE mesg varchar(20);
SELECT pnum INTO var FROM product where pid=NEW.pid;
IF var<NEW.onum 
   THEN  SELECT XXXX INTO mesg;
ELSE 
   UPDATE product SET pnum=pnum-NEW.onum where pid=NEW.pid;
END IF; 
END

插入訂單,看看如果庫(kù)存不夠是否還能夠插入成功。
INSERT INTO orders(pid, onum)VALUES(1, 110);

2、實(shí)現(xiàn)安全

A、限制插入記錄的日期
在訂單表上創(chuàng)建插入觸發(fā)器,周六周日不允許下訂單。

create trigger trigger_limitDate
BEFORE INSERT ON orders FOR EACH ROW
BEGIN
DECLARE mesg varchar(10);
IF DAYNAME(now())='sunday' or DAYNAME(now())='saturday' 
   THEN SELECT XXXXX INTO mesg;
ELSE 
   SET mesg='允許插入訂單';
END IF;
END

驗(yàn)證上面創(chuàng)建的觸發(fā)器是否工作正常,看看當(dāng)前時(shí)間是否是周六周日,向訂單表插入記錄,檢查是否能夠成功。
insert into orders(pid,onum) values (3,30)
B、限制數(shù)據(jù)更改的范圍
在產(chǎn)品表上創(chuàng)建更新觸發(fā)器,限制產(chǎn)品價(jià)格一次上調(diào)不能超過(guò)20%。
觸發(fā)器設(shè)置成before update,在更改前檢查價(jià)格增長(zhǎng)幅度是否超過(guò)20%,如果超過(guò)就產(chǎn)生錯(cuò)誤,取消操作。
更新操作分為兩步,第一步是刪除原來(lái)的記錄,第二步是插入新記錄。原來(lái)的記錄在old表中,新記錄在new表中。觸發(fā)器中new.price存放的是新價(jià)格,old.price是原來(lái)的價(jià)格。

create trigger trigger_limitIncreasePrice
BEFORE UPDATE ON product FOR EACH ROW
BEGIN
DECLARE mesg varchar(10);
if (NEW.price-OLD.price)*100/OLD.price > 20 
   then select XXXX into mesg;
else 
   set mesg='更改成功';
end if;
END

驗(yàn)證觸發(fā)器
update product set price=20 where pid=1;

3、實(shí)現(xiàn)數(shù)據(jù)完整性

使用觸發(fā)器可以限制表插入某列的數(shù)值范圍。
創(chuàng)建一個(gè)學(xué)生表,有四列,姓名、性別、手機(jī)和郵箱。

create table personinfo
(
sname VARCHAR(5),
sex CHAR(1),
phone VARCHAR(11)
)ENGINE=innoDB default CHARSET=utf8;

A、指定性別列的取值范圍
創(chuàng)建觸發(fā)器,限制性別列,只允許輸入“男”和“女”。before insert觸發(fā)器,不滿(mǎn)足條件執(zhí)行有錯(cuò)誤的SQL語(yǔ)句,退出。

create trigger trigger_limitSex
before insert on personinfo for each row
begin
declare mesg varchar(10);
if NEW.sex='男' or NEW.sex='女' 
then
   set mesg='更改成功';
else 
   select xxxx into mesg;
end if;
End

驗(yàn)證觸發(fā)器

insert into personinfo VALUES('孫悟空', '難','18900000000');
insert into personinfo VALUES('唐僧', '男','18900000001');

B、限制手機(jī)列的取值類(lèi)型和長(zhǎng)度
創(chuàng)建觸發(fā)器,只允許phone列輸入的手機(jī)號(hào)只能是11位數(shù)字,且第一位數(shù)字是1。

create trigger trigger_limitPhone
before insert on personinfo for each row
begin
declare mesg varchar(10);
if NEW.phone regexp '[1][0-9]{10}' 
   then set mesg='插入成功';
else 
   select xxxx into mesg;
end if;
End

驗(yàn)證觸發(fā)器,如果手機(jī)列插入的值位數(shù)不對(duì)或者第一位不是1,插入都將失敗。
insert into personinfo VALUES('唐僧', '男','2890000001');

4、使用觸發(fā)器審計(jì)

使用觸發(fā)器實(shí)現(xiàn)對(duì)personinfo表數(shù)據(jù)操作的跟蹤,將跟蹤事件記錄到一張審計(jì)表中review。

create table review
(
username VARCHAR(20),
action VARCHAR(10),
studentID CHAR(5),
sname CHAR(10),
actionTime TIMESTAMP
);

A、創(chuàng)建觸發(fā)器記錄插入操作

create trigger trigger_insert
before insert on personinfo for each row
begin
insert into review values(user(),'insert',new.sname,now());
End

插入personinfo表一條記錄
insert into personinfo values('孫悟空', '男', '13008080808');
查看review表中增加的INSERT記錄
select * from review
MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器
B、創(chuàng)建觸發(fā)器記錄刪除操作

create trigger trigger_delete
after DELETE on personinfo for each row
begin
insert into review values(user(),'delete',old.sname,now());
End

從personinfo刪除一條記錄
delete from personinfo where sname='孫悟空';
查看reivew表中增加的DELETE記錄
MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器
C、創(chuàng)建觸發(fā)器記錄修改操作

create trigger trigger_update
after UPDATE on personinfo for each row
begin
insert review values(user(),'update',new.sname,now());
End

更新personinfo表中名字為‘孫悟空’的phone。
update personinfo set phone='189080808' where sname='孫悟空';
查看reivew表中增加的UPDATE記錄
MySQL數(shù)據(jù)庫(kù)高級(jí)(五)——觸發(fā)器

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

免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI