SQL觸發器實例講解

　　SQL觸發器實例 
定義 何為觸發器？在SQL Server裡面也就是對某一個表的一定的操作觸發某種條件從而執行的一段程序觸發器是一個特殊的存儲過程 
常見的觸發器有三種分別應用於Insert Update Delete 事件 
我為什麼要使用觸發器？比如這麼兩個表 
復制代碼代碼如下:
Create Table Student( 學生表 
StudentID int primary key 學號 
 
) 
Create Table BorrowRecord( 學生借書記錄表 
BorrowRecord int identity() 流水號 
StudentID int 學號 
BorrowDate datetime 借出時間 
ReturnDAte Datetime 歸還時間 
 
) 
用到的功能有: 
如果我更改了學生的學號我希望他的借書記錄仍然與這個學生相關(也就是同時更改借書記錄表的學號); 
如果該學生已經畢業我希望刪除他的學號的同時也刪除它的借書記錄 
等等 
這時候可以用到觸發器對於創建一個Update觸發器 
復制代碼代碼如下:
Create Trigger truStudent 
On Student 在Student表中創建觸發器 
for Update 為什麼事件觸發 
As 事件觸發後所要做的事情 
if Update(StudentID) 
begin 
Update BorrowRecord 
Set StudentID=iStudentID 
From BorrowRecord br Deleted d Inserted i Deleted和Inserted臨時表 
Where brStudentID=dStudentID 
end 
理解觸發器裡面的兩個臨時的表Deleted Inserted 注意Deleted 與Inserted分別表示觸發事件的表“舊的一條記錄”和“新的一條記錄” 
一個數據庫系統中有兩個虛擬表用於存儲在表中記錄改動的信息分別是 
虛擬表Inserted 虛擬表Deleted 
在表記錄新增時 存放新增的記錄 不存儲記錄 
修改時 存放用來更新的新記錄 存放更新前的記錄 
刪除時 不存儲記錄 存放被刪除的記錄 
一個Update 的過程可以看作為生成新的記錄到Inserted表復制舊的記錄到Deleted表然後刪除Student記錄並寫入新紀錄 
對於創建一個Delete觸發器 
復制代碼代碼如下:
Create trigger trdStudent 
On Student 
for Delete 
As 
Delete BorrowRecord 
From BorrowRecord br Delted d 
Where brStudentID=dStudentID 
從這兩個例子我們可以看到了觸發器的關鍵A個臨時的表B觸發機制 
SQL觸發器實例 
復制代碼代碼如下:
/* 
建立虛擬測試環境包含表[卷煙庫存表]表[卷煙銷售表] 
請大家注意跟蹤這兩個表的數據體會觸發器到底執行了什麼業務邏輯對數據有什麼影響 
為了能更清晰的表述觸發器的作用表結構存在數據冗余且不符合第三范式這裡特此說明 
*/ 
USE Master 
GO 
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷煙庫存表) 
DROP TABLE 卷煙庫存表 
GO 
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = U AND NAME = 卷煙銷售表) 
DROP TABLE 卷煙銷售表 
GO 
業務規則銷售金額 = 銷售數量 * 銷售單價 業務規則 
CREATE TABLE 卷煙銷售表 
( 
卷煙品牌 VARCHAR() PRIMARY KEY NOT NULL 
購貨商 VARCHAR() NULL 
銷售數量 INT NULL 
銷售單價 MONEY NULL 
銷售金額 MONEY NULL 
) 
GO 
業務規則庫存金額 = 庫存數量 * 庫存單價 業務規則 
CREATE TABLE 卷煙庫存表 
( 
卷煙品牌 VARCHAR() PRIMARY KEY NOT NULL 
庫存數量 INT NULL 
庫存單價 MONEY NULL 
庫存金額 MONEY NULL 
) 
GO 
創建觸發器示例 
/* 
創建觸發器[T_INSERT_卷煙庫存表]這個觸發器較簡單 
說明 每當[卷煙庫存表]發生 INSERT 動作則引發該觸發器 
觸發器功能 強制執行業務規則保證插入的數據中庫存金額 = 庫存數量 * 庫存單價 
注意 [INSERTED][DELETED]為系統表不可創建修改刪除但可以調用 
重要 這兩個系統表的結構同插入數據的表的結構 
*/ 
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷煙庫存表) 
DROP TRIGGER T_INSERT_卷煙庫存表 
GO 
CREATE TRIGGER T_INSERT_卷煙庫存表 
ON 卷煙庫存表 
FOR INSERT 
AS 
提交事務處理 
BEGIN TRANSACTION 
強制執行下列語句保證業務規則 
UPDATE 卷煙庫存表 
SET 庫存金額 = 庫存數量 * 庫存單價 
WHERE 卷煙品牌 IN (SELECT 卷煙品牌 from INSERTED) 
COMMIT TRANSACTION 
GO 
/* 
針對[卷煙庫存表]插入測試數據 
注意第一條數據（紅塔山新勢力）中的數據符合業務規則 
第二條數據（紅塔山人為峰）中[庫存金額]空不符合業務規則 
第三條數據（雲南映像）中[庫存金額]不等於[庫存數量]乘以[庫存單價]不符合業務規則 
第四條數據庫存數量為 
請注意在插入數據後檢查[卷煙庫存表]中的數據是否 庫存金額 = 庫存數量 * 庫存單價 
*/ 
INSERT INTO 卷煙庫存表(卷煙品牌庫存數量庫存單價庫存金額) 
SELECT 紅塔山新勢力 UNION ALL 
SELECT 紅塔山人為峰NULL UNION ALL 
SELECT 雲南映像 UNION ALL 
SELECT 玉溪 
GO 
查詢數據 
SELECT * FROM 卷煙庫存表 
GO 
/* 
結果集 
RecordId 卷煙品牌 庫存數量 庫存單價 庫存金額 
 
紅塔山新勢力  
紅塔山人為峰  
雲南映像  
玉溪  
（所影響的行數為 行） 
*/ 
觸發器示例 
/* 
創建觸發器[T_INSERT_卷煙銷售表]該觸發器較復雜 
說明: 每當[卷煙庫存表]發生 INSERT 動作則引發該觸發器 
觸發器功能 實現業務規則 
業務規則: 如果銷售的卷煙品牌不存在庫存或者庫存為零則返回錯誤 
否則則自動減少[卷煙庫存表]中對應品牌卷煙的庫存數量和庫存金額 
*/ 
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = TR AND NAME = T_INSERT_卷煙銷售表) 
DROP TRIGGER T_INSERT_卷煙銷售表 
GO 
CREATE TRIGGER T_INSERT_卷煙銷售表 
ON 卷煙銷售表 
FOR INSERT 
AS 
BEGIN TRANSACTION 
檢查數據的合法性銷售的卷煙是否有庫存或者庫存是否大於零 
IF NOT EXISTS ( 
SELECT 庫存數量 
FROM 卷煙庫存表 
WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) 
) 
BEGIN 
返回錯誤提示 
RAISERROR(錯誤！該卷煙不存在庫存不能銷售) 
回滾事務 
ROLLBACK 
RETURN 
END 
IF EXISTS ( 
SELECT 庫存數量 
FROM 卷煙庫存表 
WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) AND 
庫存數量 <=  
) 
BEGIN 
返回錯誤提示 
RAISERROR(錯誤！該卷煙庫存小於等於不能銷售) 
回滾事務 
ROLLBACK 
RETURN 
END 
對合法的數據進行處理 
強制執行下列語句保證業務規則 
UPDATE 卷煙銷售表 
SET 銷售金額 = 銷售數量 * 銷售單價 
WHERE 卷煙品牌 IN (SELECT 卷煙品牌 FROM INSERTED) 
DECLARE @卷煙品牌 VARCHAR() 
SET @卷煙品牌 = (SELECT 卷煙品牌 FROM INSERTED) 
DECLARE @銷售數量 MONEY 
SET @銷售數量 = (SELECT 銷售數量 FROM INSERTED) 
UPDATE 卷煙庫存表 
SET 庫存數量 = 庫存數量 @銷售數量 
庫存金額 = (庫存數量 @銷售數量)*庫存單價 
WHERE 卷煙品牌 = @卷煙品牌 
COMMIT TRANSACTION 
GO 
請大家自行跟蹤[卷煙庫存表]和[卷煙銷售表]的數據變化 
針對[卷煙銷售表]插入第一條測試數據該數據是正常的 
INSERT INTO 卷煙銷售表(卷煙品牌購貨商銷售數量銷售單價銷售金額) 
SELECT 紅塔山新勢力某購貨商 
GO 
針對[卷煙銷售表]插入第二條測試數據該數據 銷售金額 不等於 銷售單價 * 銷售數量 
觸發器將自動更正數據使 銷售金額 等於 銷售單價 * 銷售數量 
INSERT INTO 卷煙銷售表(卷煙品牌購貨商銷售數量銷售單價銷售金額) 
SELECT 紅塔山人為峰某購貨商 
GO 
針對[卷煙銷售表]插入第三條測試數據該數據中的卷煙品牌在 卷煙庫存表中找不到對應 
觸發器將報錯 
INSERT INTO 卷煙銷售表(卷煙品牌購貨商銷售數量銷售單價銷售金額) 
SELECT 紅河V某購貨商 
GO 
/* 
結果集 
服務器: 消息 級別 狀態 過程 T_INSERT_卷煙銷售表行  
錯誤！該卷煙不存在庫存不能銷售 
*/ 
針對[卷煙銷售表]插入第三條測試數據該數據中的卷煙品牌在 卷煙庫存表中庫存為 
觸發器將報錯 
INSERT INTO 卷煙銷售表(卷煙品牌購貨商銷售數量銷售單價銷售金額) 
SELECT 玉溪某購貨商 
GO 
/* 
結果集 
服務器: 消息 級別 狀態 過程 T_INSERT_卷煙銷售表行  
錯誤！該卷煙庫存小於等於不能銷售 
*/ 
查詢數據 
SELECT * FROM 卷煙庫存表 
SELECT * FROM 卷煙銷售表 
GO 
/* 
補充 
本示例主要通過一個簡單的業務規則實現來進行觸發器使用的說明具體的要根據需要靈活處理 
關於觸發器要理解並運用好 INSERTED DELETED 兩個系統表 
本示例創建的觸發器都是 FOR INSERT 具體的語法可參考 
Trigger語法 
復制代碼代碼如下:
CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 用於加密觸發器 
{ 
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ ] [ UPDATE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS 
[ { IF UPDATE ( column ) 
[ { AND | OR } UPDATE ( column ) ] 
[ n ] 
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) 
{ comparison_operator } column_bitmask [ n ] 
} ] 
sql_statement [ n ] 
} 
} 
關於觸發器還應該注意 
()DELETE 觸發器不能捕獲 TRUNCATE TABLE 語句 
()觸發器中不允許以下 TransactSQL 語句 
ALTER DATABASE CREATE DATABASE DISK INIT 
DISK RESIZE DROP DATABASE LOAD DATABASE 
LOAD LOG RECONFIGURE RESTORE DATABASE 
RESTORE LOG 
()觸發器最多可以嵌套 層 
*/ 
修改觸發器 
實質上是將 CREATE TRIGGER 修改為 ALTER TRIGGER 即可 
刪除觸發器 
DROP TRIGGER xxx 
GO 
刪除測試環境 
DROP TABLE 卷煙庫存表 
GO 
DROP TABLE 卷煙銷售表 
GO 
DROP TRIGGER T_INSERT_卷煙庫存表 
GO 
DROP TRIGGER T_INSERT_卷煙銷售表 
GO 
################################################################## 
觸發器的基礎知識和例子 
create trigger tr_name 
on table/view 
{for | after | instead of } [update][][insert][][delete] 
[with encryption] 
as {batch | if update (col_name) [{and|or} update (col_name)] } 
說明 
tr_name 觸發器名稱 
on table/view 觸發器所作用的表一個觸發器只能作用於一個表 
for 和after 同義 
after 與instead of :sql 新增項目afrer 與 instead of 的區別 
After 
在觸發事件發生以後才被激活只可以建立在表上 
Instead of 
代替了相應的觸發事件而被執行既可以建立在表上也可以建立在視圖上 
insertupdatedelete激活觸發器的三種操作可以同時執行也可選其一 
if update (col_name)表明所作的操作對指定列是否有影響有影響則激活觸發器此外因為delete 操作只對行有影響 
所以如果使用delete操作就不能用這條語句了(雖然使用也不出錯但是不能激活觸發器沒意義) 
觸發器執行時用到的兩個特殊表deleted inserted 
deleted 和inserted 可以說是一種特殊的臨時表是在進行激活觸發器時由系統自動生成的其結構與觸發器作用的表結構是一 
樣的只是存放 的數據有差異 
續 
下面表格說明deleted 與inserted 數據的差異 
deleted 與inserted 數據的差異 
Inserted 
存放進行insert和update 操作後的數據 
Deleted 
存放進行delete 和update操作前的數據 
注意update 操作相當於先進行delete 再進行insert 所以在進行update操作時修改前的數據拷貝一條到deleted 表中修改後 
的數據在存到觸發器作用的表的同時也同時生成一條拷貝到insered表中

From:http://tw.wingwit.com/Article/program/MySQL/201311/29568.html