一,、觸發(fā)器簡介
1,、觸發(fā)器簡介
觸發(fā)器是和表關(guān)聯(lián)的特殊的存儲過程，可以在插入,，刪除或修改表中的數(shù)據(jù)時觸發(fā)執(zhí)行,，比數(shù)據(jù)庫本身標(biāo)準(zhǔn)的功能有更精細(xì)和更復(fù)雜的數(shù)據(jù)控制能力。
2,、觸發(fā)器的優(yōu)點(diǎn)
A,、安全性
可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利?？梢曰跁r間限制用戶的操作,，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫數(shù)據(jù)；可以基于數(shù)據(jù)庫中的數(shù)據(jù)限制用戶的操作,，例如不允許股票的價格的升幅一次超過10%,。
B、審計(jì)
可以跟蹤用戶對數(shù)據(jù)庫的操作,。 審計(jì)用戶操作數(shù)據(jù)庫的語句,；把用戶對數(shù)據(jù)庫的更新寫入審計(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ù)庫對象,。例如，觸發(fā)器可回退任何企圖吃進(jìn)超過自己保證金的期貨,。提供可變的缺省值,。
D、實(shí)現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫相關(guān)完整性規(guī)則,。
觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進(jìn)行連環(huán)更新,。
在修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行。
在修改或刪除時把其它表中的與之匹配的行設(shè)成NULL值,。
在修改或刪除時把其它表中的與之匹配的行級聯(lián)設(shè)成缺省值,。
觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進(jìn)行數(shù)據(jù)更新的事務(wù),。
E,、同步實(shí)時地復(fù)制表中的數(shù)據(jù)。
F,、SQL觸發(fā)器提供了運(yùn)行計(jì)劃任務(wù)的另一種方法,。自動計(jì)算數(shù)據(jù)值,，如果數(shù)據(jù)的值達(dá)到了一定的要求，則進(jìn)行特定的處理,。例如,，如果公司的賬號上的資金低于5萬元則立即給財(cái)務(wù)人員發(fā)送警告數(shù)據(jù)。
3,、觸發(fā)器的限制
A,、觸發(fā)程序不能調(diào)用將數(shù)據(jù)返回客戶端的存儲程序，也不能使用采用CALL語句的動態(tài)SQL語句,，但是允許存儲程序通過參數(shù)將數(shù)據(jù)返回觸發(fā)程序,，也就是存儲過程或者函數(shù)通過OUT或者INOUT類型的參數(shù)將數(shù)據(jù)返回觸發(fā)器是可以的，但是不能調(diào)用直接返回?cái)?shù)據(jù)的過程,。
B,、不能在觸發(fā)器中使用以顯示或隱式方式開始或結(jié)束事務(wù)的語句，如START TRANS-ACTION,COMMIT或ROLLBACK,。
二,、觸發(fā)器的使用
1、創(chuàng)建觸發(fā)器
創(chuàng)建觸發(fā)器的語法：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：觸發(fā)器的名稱,。
tirgger_time：觸發(fā)時機(jī),，為BEFORE或者AFTER。
trigger_event：觸發(fā)事件,，為INSERT,、DELETE或者UPDATE。
tb_name：表示建立觸發(fā)器的表名,，在哪張表上建立觸發(fā)器,。
trigger_stmt：觸發(fā)器的程序體，可以是一條SQL語句或者是用BEGIN和END包含的多條語句,。
FOR EACH ROW表示任何一條記錄上的操作滿足觸發(fā)事件都會觸發(fā)該觸發(fā)器,。
MySQL除了對INSERT、UPDATE,、DELETE基本操作進(jìn)行定義外,，還定義了LOAD DATA和REPLACE語句,，這兩種語句也能引起上述6中類型的觸發(fā)器的觸發(fā),。
LOAD DATA 語句用于將一個文件裝入到一個數(shù)據(jù)表中，相當(dāng)與一系列的 INSERT操作,。
REPLACE語句一般來說和INSERT語句很像,，只是在表中有primary key或 unique索引時,，如果插入的數(shù)據(jù)和原來primary key或unique索引一致時，會先刪除原來的數(shù)據(jù)，然后增加一條新數(shù)據(jù),。
INSERT型觸發(fā)器：插入某一行時激活觸發(fā)器,，通過 INSERT、LOAD DATA,、REPLACE語句觸發(fā),；
UPDATE型觸發(fā)器：更改某一行時激活觸發(fā)器,，通過UPDATE語句觸發(fā),；
DELETE型觸發(fā)器：刪除某一行時激活觸發(fā)器，通過DELETE,、REPLACE語句觸發(fā),。
變量聲明：
DECLARE var_name[,...] type [DEFAULT value]
對變量賦值采用SET 語句，語法為：
SET var_name = expr [,var_name = expr] ...
MySQL中定義了NEW和OLD,，用來表示觸發(fā)器的所在表中,，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)。
在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為相應(yīng)數(shù)據(jù)表某一列名）
另外,，OLD是只讀的，而NEW則可以在觸發(fā)器中使用SET賦值,，不會再次觸發(fā)觸發(fā)器,，造成循環(huán)調(diào)用。
2,、刪除觸發(fā)器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
schema_name是數(shù)據(jù)庫的名稱,，是可選的。如果省略了schema,，將從當(dāng)前數(shù)據(jù)庫中舍棄觸發(fā)程序,。trigger_name是要刪除的觸發(fā)器的名稱。
3,、觸發(fā)器信息查看
在MySQL中,，所有的觸發(fā)器的定義都存在于INFORMATION_SCHEMA數(shù)據(jù)庫的triggers表中，可以通過查詢命令SELECT來查看,，具體語法如下：
SHOW TRIGGERS [FROM schema_name];
觸發(fā)器的執(zhí)行順序
InnoDB數(shù)據(jù)庫,，若SQL語句或觸發(fā)器執(zhí)行失敗，MySQL會回滾事務(wù)，有：
A,、如果BEFORE觸發(fā)器執(zhí)行失敗,，SQL無法正確執(zhí)行。
B,、SQL執(zhí)行失敗時,，AFTER型觸發(fā)器不會觸發(fā)。
C,、AFTER類型的觸發(fā)器執(zhí)行失敗,，SQL會回滾。
MySQL的觸發(fā)器是按照BEFORE觸發(fā)器,、行操作,、AFTER觸發(fā)器的順序執(zhí)行的，其中任何一步發(fā)生錯誤都不會繼續(xù)執(zhí)行剩下的操作,，如果對事務(wù)表進(jìn)行的操作,，如果出現(xiàn)錯誤，那么將會被回滾,，如果對非事務(wù)表進(jìn)行操作,，那么就無法回滾，數(shù)據(jù)可能會出錯,。
三,、觸發(fā)器應(yīng)用
1、實(shí)現(xiàn)業(yè)務(wù)邏輯
客戶下訂單訂購商品,，商品表自動減少數(shù)量,。
在商品表創(chuàng)建刪除觸發(fā)器，刪除某商品,，自動刪除該商品的訂單,。
創(chuàng)建產(chǎn)品表，有產(chǎn)品編號,，產(chǎn)品名稱,、產(chǎn)品數(shù)量和產(chǎn)品價格四列，其中產(chǎn)品編號自增長列,，并設(shè)置成主鍵,。
create table product
(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10),
price DOUBLE,
pnum INT
)ENGINE=innoDB default CHARSET=utf8;
創(chuàng)建訂單表，有三列,，訂單編號,、產(chǎn)品編號和數(shù)量，其中訂單編號自增長列,，并設(shè)置成主鍵,。
create table orders
(
oid INT PRIMARY KEY AUTO_INCREMENT,
pid INT,
onum INT
)ENGINE=innoDB DEFAULT CHARSET=utf8;
插入三種產(chǎn)品，產(chǎn)品名稱和數(shù)量以及價格。
insert into product(pname, pnum, price)values('桃子', 100, 2);
insert into product(pname, pnum, price)values('蘋果', 80, 8);
insert into product(pname, pnum, price)values('香蕉', 50, 5);
在訂單表上創(chuàng)建觸發(fā)器,，當(dāng)有訂單,，會根據(jù)訂單的產(chǎn)品編號和數(shù)量自動減少產(chǎn)品的數(shù)量。觸發(fā)器中NEW代表一個表,，存放插入的訂單記錄,。
create trigger trigger_order
AFTER INSERT ON orders FOR EACH ROW
BEGIN
UPDATE product SET pnum=pnum-NEW.onum where pid = NEW.pid;
END
插入兩個訂單
INSERT INTO orders(pid, onum)VALUES(1, 10);
INSERT INTO orders(pid, onum)VALUES(2, 5);
查看產(chǎn)品表，可以看到對應(yīng)的產(chǎn)品數(shù)量減少,。操作由訂單表的Insert觸發(fā)器完成,。
在訂單表上創(chuàng)建新的觸發(fā)器，當(dāng)訂單定的某產(chǎn)品產(chǎn)品數(shù)量大于產(chǎn)品庫存,，禁止下訂單,，也就是禁止在訂單表中插入記錄。
一張表中只能有一個INSERT類型的觸發(fā)器,，先刪除INSERT觸發(fā)器。
drop trigger trigger_order;
MySQL不能在觸發(fā)器中通過回滾事務(wù)取消操作,，但如果觸發(fā)器的SQL語句執(zhí)行過程中出現(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
插入訂單,，看看如果庫存不夠是否還能夠插入成功。
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)前時間是否是周六周日，向訂單表插入記錄,，檢查是否能夠成功,。
insert into orders(pid,onum) values (3,30)
B、限制數(shù)據(jù)更改的范圍
在產(chǎn)品表上創(chuàng)建更新觸發(fā)器,，限制產(chǎn)品價格一次上調(diào)不能超過20%,。
觸發(fā)器設(shè)置成before update，在更改前檢查價格增長幅度是否超過20%,，如果超過就產(chǎn)生錯誤,，取消操作。
更新操作分為兩步,，第一步是刪除原來的記錄,，第二步是插入新記錄。原來的記錄在old表中，新記錄在new表中,。觸發(fā)器中new.price存放的是新價格,，old.price是原來的價格。
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)建一個學(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ā)器，不滿足條件執(zhí)行有錯誤的SQL語句,，退出,。
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ī)列的取值類型和長度
創(chuàng)建觸發(fā)器,，只允許phone列輸入的手機(jī)號只能是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ù)不對或者第一位不是1,，插入都將失敗。
insert into personinfo VALUES('唐僧', '男','2890000001');
4,、使用觸發(fā)器審計(jì)
使用觸發(fā)器實(shí)現(xiàn)對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
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記錄
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記錄
|
