觸發器簡介
觸發器是和表關聯的特殊的存儲過程，可以在插入，刪除或修改表中的數據時觸發執行，比數據庫本身標準的功能有更精細和更復雜的數據控制能力。
觸發器的優點：
- 安全性：可以基于數據庫的值使用戶具有操作數據庫的某種權利。例如不允許下班后和節假日修改數據 庫數據；
- 審計：可以跟蹤用戶對數據庫的操作；
- 實現復雜的數據完整性規則。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨；
- 提供了運行計劃任務的另一種方法。例如，如果公司的帳號上的資金低于 5 萬元則立即給財務人員發送 警告數據。
MySQL 中使用觸發器
創建觸發器
創建觸發器的技巧就是記住觸發器的四要素：
- 監控地點：table；
- 監控事件：insert/update/delete；
- 觸發時間：after/before；
- 觸發事件：insert/update/delete。
創建觸發器的基本語法如下所示：
CREATE TRIGGER
-- trigger_name：觸發器的名稱；
-- tirgger_time：觸發時機，為 BEFORE 或者 AFTER；
-- trigger_event：觸發事件，為 INSERT、DELETE 或者 UPDATE；
trigger_name trigger_time trigger_event
ON
-- tb_name：表示建立觸發器的表名，在哪張表上建立觸發器；
tb_name
-- FOR EACH ROW 表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器。
FOR EACH ROW
-- trigger_stmt：觸發器的程序體，可以是一條 SQL 語句或者是用 BEGIN 和 END 包含的多條語句；
trigger_stmt
- trigger_name：觸發器的名稱；
- tirgger_time：觸發時機，為 BEFORE 或者 AFTER；
- trigger_event：觸發事件，為 INSERT、DELETE 或者 UPDATE；
- tb_name：表示建立觸發器的表名，在哪張表上建立觸發器；
- trigger_stmt：觸發器的程序體，可以是一條 SQL 語句或者是用 BEGIN 和 END 包含的多條語句；
- FOR EACH ROW 表示任何一條記錄上的操作滿足觸發事件都會觸發該觸發器。
注意：對同一個表相同觸發時間的相同觸發事件，只能定義一個觸發器。
觸發器新舊記錄
MySQL 中定義了 NEW 和 OLD，用來表示觸發器的所在表中，觸發了觸發器的那一行數據：
- 在 INSERT 型觸發器中，NEW 用來表示將要（BEFORE或已經（AFTER）插入的新數據；
- 在 UPDATE型觸發器中，OLD 用來表示將要或已經被修改的原數據，NEW 用來表示將要或已經修改為的新 數據；
- 在 DELETE型觸發器中，OLD 用來表示將要或已經被刪除的原數據。
創建觸發器，當用戶購買商品時，同時更新對應商品庫存記錄，代碼如下所示：
-- 刪除觸發器，drop trigger 觸發器名稱
-- if exists判斷存在才會刪除
drop trigger if exists myty1;
-- 創建觸發器
create trigger mytg1-- myty1觸發器的名稱
after insert on orders-- orders在哪張表上建立觸發器；
for each row
begin
update product set num = num-new.num where pid=new.pid;
end;
-- 往訂單表插入記錄
insert into orders values(null,2,1);
-- 查詢商品表商品庫存更新情況
select * from product;
創建觸發器，當用戶刪除訂單時，同時更新對應商品庫存記錄，代碼如下所示：
-- 創建觸發器
create trigger mytg2
after delete on orders
for each ROW
begin
-- 對庫存進行回退，重新加上
update product set num = num+old.num where pid=old.pid;
end;
-- 刪除訂單記錄
delete from orders where oid = 2;
-- 查詢商品表商品庫存更新情況
select * from product;
before 和 after 的區別
before 在執行語句之前after 在執行語句之后
當訂單商品數量超過庫存時，修改訂單數量為最大庫存：
-- -- 創建 before 觸發器
create trigger mytg3
before insert on orders
for each row
begin
-- 定義一個變量，來接收庫存
declare n int default 0;
-- 查詢庫存 把num賦值給n
select num into n from product where pid = new.pid;
-- 判斷下單的數量是否大于庫存量
if new.num>n then
-- 大于修改下單庫存（庫存改為最大量）
set new.num = n;
end if;
update product set num = num-new.num where pid=new.pid;
end;
-- 往訂單表插入記錄
insert into orders values(null,3,50);
-- 查詢商品表商品庫存更新情況
select * from product;
-- 查詢訂單表
select * from orders;
游標
游標簡介
游標的作用就是用于對查詢數據庫所返回的記錄進行遍歷，以便進行相應的操作。游標有下面這些特征
- 游標是只讀的，也就是不能更新它；
- 游標是不能滾動的，也就是只能在一個方向上進行遍歷，不能在記錄之間隨意進退，不能跳過某些記錄；
- 避免在已經打開游標的表上更新數據。
創建游標
創建游標的語法包含四個部分：
- 定義游標：declare 游標名 cursor for select 語句；
- 打開游標：open 游標名；
- 獲取結果：fetch游標名 into 變量名[,變量名]；
- 關閉游標:close 游標名；
創建一個過程 p1，使用游標返回 test 數據庫中 student 表的第一個學生信息。代碼如下所示：
-- 定義過程
create procedure p1()
begin
declare id int;
declare name varchar(20);
declare age int;
-- 定義游標 declare 游標名 cursor for select 語句；
declare mc cursor for select * from student;
-- 打開游標 open 游標名；
open mc;
-- 獲取數據 fetch 游標名 into 變量名[,變量名]；
fetch mc into id,name,age;
-- 打印
select id,name,age;
-- 關閉游標
close mc;
end;
-- 調用過程
call p1();
在 test 數據庫創建一個 student2 表，創建一個過程 p2，使用游標提取 student 表中所有學生信息插入到 student2 表中。代碼如下所示：
-- 定義過程
create procedure p3()
begin
declare id int;
declare name varchar(20);
declare age int;
declare flag int default 0;
-- 定義游標 declare 游標名 cursor for select 語句；
declare mc cursor for select * from student;
declare continue handler for not found set flag=1;
-- 打開游標 open 游標名；
open mc;
-- 獲取數據 fetch 游標名 into 變量名[,變量名]；
a:loop -- 循環獲取數據
fetch mc into id,name,age;
if flag=1 then -- 當無法fetch時觸發continue handler
leave a;-- 終止循環
end if;
-- 進行遍歷，將提取的每一行數據插入到 student2 表中
insert into student2 values(id,name,age);
end loop;
-- 關閉游標
close mc;
end;
-- 調用過程
call p3();
-- 查詢 student2 表
select * from student2;
總結
到此這篇關于MySQL中觸發器和游標的文章就介紹到這了,更多相關MySQL觸發器和游標內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySQL系列之五 視圖、存儲函數、存儲過程、觸發器
- MySQL觸發器的使用
- mysql觸發器trigger實例詳解
- MySQL 觸發器的使用和理解
- 一篇文章帶你深入了解Mysql觸發器
