MySQL從刪庫到跑路_高級（五）——觸發器

来源:https://www.cnblogs.com/cxydczzl/archive/2018/11/07/9921630.html
-Advertisement-
Play Games

作者：天山老妖S 鏈接：http://blog.51cto.com/9291927 一、觸發器簡介 1、觸發器簡介 觸發器是和表關聯的特殊的存儲過程，可以再插入，刪除或修改表中的數據時觸發執行，比資料庫本身標準的功能有更精細和更複雜的數據控制能力。 2、觸發器的優點 A、安全性 可以基於資料庫的值使 ...


作者：天山老妖S

鏈接：http://blog.51cto.com/9291927

一、觸發器簡介

1、觸發器簡介

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

2、觸發器的優點

A、安全性

可以基於資料庫的值使用戶具有操作資料庫的某種權利。可以基於時間限制用戶的操作，例如不予許下班後和節假日修改資料庫數據；可以基於資料庫中的數據限制用戶的操作，例如不允許股票的價格的升幅一次超過10%。

B、審計

可以跟蹤用戶對資料庫的操作。審計用戶操作資料庫的語句；把用戶對資料庫的更新寫入審計表。

C、實現複雜的數據完整性規則

實現非標準的數據完整性檢查和約束。觸發器可產生比規則更為複雜的限制。與規則不同，觸發器可以引用列或資料庫對象。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。提供可變的預設值。

D、實現複雜的非標準的資料庫相關完整性規則。

觸發器可以對資料庫中相關的表進行連環更新。

在修改或刪除時級聯修改或刪除其它表中的與之匹配的行。

在修改或刪除時把其它表中的與之匹配的行設成NULL值。

在修改或刪除時把其它表中的與之匹配的行級聯設成預設值。

觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行數據更新的事務。

E、同步實時地複製表中的數據。

F、SQL觸發器提供了運行計劃任務的另一種方法。自動計算數據值，如果數據的值達到一定的要求，則進行特定的處理。例如，如果公司的賬號上的資金低於5萬元則立即給財務人員發送警告數據。

3、觸發器的限制

A、觸發程式不能調用將數據返回客戶端的存儲程式，也不能使用採用CALL語句的動態SQL語句，但是允許存儲程式通過參數將數據返回觸發程式，也就是存儲過程或者函數通過OUT或者INOUT類型的參數將數據返回觸發器是可以的，但是不能調用直接返回數據的過程。

B、不能在觸發器中使用以顯示或隱式方式開始或結束事務的語句，如 SELECT TRANS-ACTION,COMMIT或ROLLBACK。

二、觸發器的使用

1、創建觸發器

創建觸發器的語法：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

trigger_name：觸發器的名稱。

trigger_time：觸發時機，為BEFIRE或者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 索引一致時，會先刪除原來的數據，然後增加一條新數據。

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 = pname-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
BEGINDECLARE 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_limitDateBEFORE INSERT ON orders FOR EACH ROWBEGINDECLARE 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_limitSexbefore insert on personinfo for each rowbegindeclare 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('唐僧','男','28900000001')；

4、使用觸發器審計

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

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

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

create trigger trigger_insertbefore insert on personinfo for each rowbegininsert 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_updateafter UPDATE on personinfo for each rowbegininsert review values(user(),'update',new.sname,now());End

更新personinfo表中名字為'孫悟空'的phone。

update personinfo set phone = '189080808' where sname = '孫悟空';

查看reivew表中增加的UPDATE記錄

 

 

 

喜歡的小伙伴們可以搜索我們個人的微信公眾號“程式員的成長之路”點擊關註或掃描下方二維碼


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • MySQL5.0安裝圖解
    @[toc] 一、MYSQL的安裝 １、打開下載的mysql安裝文件mysql 5.0.27 win32.zip，雙擊解壓縮，運行“setup.exe”。 ２、選擇安裝類型，有“Typical（預設）”、“Complete（完全）”、“Custom（用戶自定義）”三個選項，選擇“Custom”，按“ ...
  • redis 系列6 數據結構之字典(下)
    一.概述 接著上篇繼續，這篇把數據結構之字典學習完， 這篇知識點包括：哈希演算法，解決鍵衝突， rehash , 漸進式rehash,字典API。 1.1 哈希演算法 當一個新的鍵值對 需要添加到字典裡面時，程式需要先根據“鍵值對”的鍵計算出哈希值和索引值，再根據索引值，將包含新“鍵值對”的哈希表節點放 ...
  • SQLServer之創建顯式事務
    顯式事務定義 顯式事務以 BEGIN TRANSACTION 語句開始，並以 COMMIT 或 ROLLBACK 語句結束。 備註 BEGIN TRANSACTION 使 @@TRANCOUNT 按 1 遞增。 BEGIN TRANSACTION 代表一點，由連接引用的數據在該點邏輯和物理上都一致的 ...
  • Can't create/write to file '/tmp/MLjnvU95' (Errcode: 13 - Permission denied)
    今天一個同事反饋往一個MySQL資料庫導入數據時，報“ERROR 1 (HY000): Can't create/write to file '/tmp/MLjnvU95' (Errcode: 13 - Permission denied)”這樣的錯誤，如下所示： uery OK, 0 rows a... ...
  • Oracle 備份恢復實例
    Oracle 備份恢復實例：三思筆記 1 shutdown abort 系統歸檔模式，有備份 create table xx as select * from emp; update xx set ename ='xxxxxx'; select * from xx [oracle@localhost ...
  • mysql截取欄位並插入到新的欄位中
    例如：在產品表product表中欄位content值為["10"]，然後在產品表中新建一個欄位product_id,提出欄位content的值10，如何實現呢？ 解： update (select id,substring(content,3,instr(content,"\"]")-3) as p ...
  • Linux（CentOS 7）安裝測試mysql5.6服務
    1.rpm -qa | grep mysql,查看原系統中是否有已經安裝得mysql。 註：centos7系統在安裝完成後，未安裝mysql任何版本。 2. rpm -e --nodeps mysql-libs-*********,如果有則將相關得卸載。 3.wget http://repo.mys ...
  • 沒有基礎可以學習大數據嗎？0基礎怎麼學習大數據？給初學者支幾招
    小白如何學習大數據技術？大數據怎麼入門？怎麼做大數據分析？數據科學需要學習那些技術？大數據的應用前景等等問題，已成為熱門大數據領域熱門問題，以下是對新手如何學習大數據技術問題的解答~ 大數據開發學習可以按照以下內容進行學習 第一階段：JavaSE+MySql+Linux 學習內容：Java 語言入門 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類