MySQL數據庫高級（五）——觸發器

MySQL數據庫高級（五）——觸發器

一、觸發器簡介

1、觸發器簡介

觸發器是和表關聯的特殊的存儲過程，可以在插入，刪除或修改表中的數據時觸發執行，比數據庫本身標準的功能有更精細和更復雜的數據控制能力。

2、觸發器的優點

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

3、觸發器的限制

A、觸發程序不能調用將數據返回客戶端的存儲程序，也不能使用采用CALL語句的動態SQL語句，但是允許存儲程序通過參數將數據返回觸發程序，也就是存儲過程或者函數通過OUT或者INOUT類型的參數將數據返回觸發器是可以的，但是不能調用直接返回數據的過程。
B、不能在觸發器中使用以顯示或隱式方式開始或結束事務的語句，如START TRANS-ACTION,COMMIT或ROLLBACK。

二、觸發器的使用

1、創建觸發器

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

2、刪除觸發器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
schema_name是數據庫的名稱，是可選的。如果省略了schema，將從當前數據庫中舍棄觸發程序。trigger_name是要刪除的觸發器的名稱。

3、觸發器信息查看

在MySQL中，所有的觸發器的定義都存在于INFORMATION_SCHEMA數據庫的triggers表中，可以通過查詢命令SELECT來查看，具體語法如下：
SHOW TRIGGERS [FROM schema_name];

觸發器的執行順序
InnoDB數據庫，若SQL語句或觸發器執行失敗，MySQL會回滾事務，有：
A、如果BEFORE觸發器執行失敗，SQL無法正確執行。
B、SQL執行失敗時，AFTER型觸發器不會觸發。
C、AFTER類型的觸發器執行失敗，SQL會回滾。
MySQL的觸發器是按照BEFORE觸發器、行操作、AFTER觸發器的順序執行的，其中任何一步發生錯誤都不會繼續執行剩下的操作，如果對事務表進行的操作，如果出現錯誤，那么將會被回滾，如果對非事務表進行操作，那么就無法回滾，數據可能會出錯。

三、觸發器應用

1、實現業務邏輯

客戶下訂單訂購商品，商品表自動減少數量。
在商品表創建刪除觸發器，刪除某商品，自動刪除該商品的訂單。
創建產品表，有產品編號，產品名稱、產品數量和產品價格四列，其中產品編號自增長列，并設置成主鍵。

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

創建訂單表，有三列，訂單編號、產品編號和數量，其中訂單編號自增長列，并設置成主鍵。

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

插入三種產品，產品名稱和數量以及價格。

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);

在訂單表上創建觸發器，當有訂單，會根據訂單的產品編號和數量自動減少產品的數量。觸發器中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);

查看產品表，可以看到對應的產品數量減少。操作由訂單表的Insert觸發器完成。

在訂單表上創建新的觸發器，當訂單定的某產品產品數量大于產品庫存，禁止下訂單，也就是禁止在訂單表中插入記錄。
一張表中只能有一個INSERT類型的觸發器，先刪除INSERT觸發器。
drop trigger trigger_order;
MySQL不能在觸發器中通過回滾事務取消操作，但如果觸發器的SQL語句執行過程中出現錯誤，會自動撤銷操作，曲線實現事務回滾。

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、實現安全

A、限制插入記錄的日期
在訂單表上創建插入觸發器，周六周日不允許下訂單。

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

驗證上面創建的觸發器是否工作正常，看看當前時間是否是周六周日，向訂單表插入記錄，檢查是否能夠成功。
insert into orders(pid,onum) values (3,30)
B、限制數據更改的范圍
在產品表上創建更新觸發器，限制產品價格一次上調不能超過20%。
觸發器設置成before update，在更改前檢查價格增長幅度是否超過20%，如果超過就產生錯誤，取消操作。
更新操作分為兩步，第一步是刪除原來的記錄，第二步是插入新記錄。原來的記錄在old表中，新記錄在new表中。觸發器中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

驗證觸發器
update product set price=20 where pid=1;

3、實現數據完整性

使用觸發器可以限制表插入某列的數值范圍。
創建一個學生表，有四列，姓名、性別、手機和郵箱。

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

A、指定性別列的取值范圍
創建觸發器，限制性別列，只允許輸入“男”和“女”。before insert觸發器，不滿足條件執行有錯誤的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

驗證觸發器

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

B、限制手機列的取值類型和長度
創建觸發器，只允許phone列輸入的手機號只能是11位數字，且第一位數字是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

驗證觸發器，如果手機列插入的值位數不對或者第一位不是1，插入都將失敗。
insert into personinfo VALUES('唐僧', '男','2890000001');

4、使用觸發器審計

使用觸發器實現對personinfo表數據操作的跟蹤，將跟蹤事件記錄到一張審計表中review。

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

A、創建觸發器記錄插入操作

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、創建觸發器記錄刪除操作

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、創建觸發器記錄修改操作

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記錄