SQL Server數據庫開發之觸發器的實際應用

說明:由于個人能力有限,文章中難免會出現錯誤或遺漏的地方,敬請諒解!同時歡迎你指出,以便我能及時修改,以免誤導下一個看官。最後希望本文能給你帶來一定的幫助。

不知道在壇子裏有多少朋友使用觸發器,如果你已經對觸發器很了解了,那麽請跳過此文,如果你還沒有使用過觸發器的話,那就讓我們來認識一下吧。

QUOTE:

定義:

觸發器是一種特殊類型的存儲過程,不由用戶直接調用。當使用下面的一種或多種數據修改操作在指定表中對數據進行修改時,觸發器會生效:UPDATE、INSERT 或 DELETE。觸發器可以查詢其它表,而且可以包含複雜的 SQL 語句。它們主要用于強制複雜的業務規則或要求。

觸發器一個應用就是保持和維護數據的完整性及合法性,那麽怎麽來理解呢?就是說你可以在程序裏提交任意數據,然後由觸發器來判斷數據的完整性及合法性,當然這裏只是舉例說明,實際應用中不推薦這樣用,應該由應用程序來驗證數據的完整性及合法性。

下面我還是以實例的方式來描述觸發器的應用。

假設:當前數據庫中有「uMateriel」和「uRecord」兩張表,他們分別用來保存物品信息和物品的出入庫記錄信息,結構如下

QUOTE:uMateriel

----------------

mIdint

mNamenvarchar(40)

mNum int DEFAULT 0

uRecord

----------------

rIdint

mIdint

rNum int

rDatedatetime DEFAULT GetDate()

rModebit DEFAULT 0

好了,數據表已經有了,現在看一下實際的應用。

現在,我們要購入物品A,數量100,時間爲當天,已知物品A的編號爲1,那麽通常我們需要做以下兩個步驟:

QUOTE:1、在 uRecord 記錄表中增加一條物品A的購入記錄:

INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)

更新 uMateriel 物品庫存表中物品A的數量:

UPDATE uMateriel SET mNum = mNum + 100 WHERE mId=1

也就是說代碼中要先後處理以上兩條語句,才能保證庫存的准確性,以ASP代碼爲例:

QUOTE:On Error Resume Next

'// 設 adoConn 爲已經連接的 ADODB.Connection 對象

With adoConn

'// 事務開始,因爲涉及到多步數據更新操作,所以在這裏使用事務

.BeginTrans

'// 插入物品入庫記錄

.Execute("INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)")

'// 更新物品庫存記錄

.Execute("UPDATE uMateriel SET mNum = mNum + 100 WHERE mId=1")

'// 判斷是否産生了錯誤

If Err.Number <> 0 Then

'// 如果有錯誤,事務回滾

.RollbackTrans

Response.Write "錯誤!"

Err.Clear

Else

'// 如果沒有錯誤,則提交事務

.CommitTrans

End If

End With

以上代碼可以更新一條入庫記錄了,但是我們今天要了解的是觸發器的應用,那麽要在觸發器裏寫什麽內容可以簡化以上代碼呢?下面來創建一個觸發器。

創建觸發器的語法很長,簡化爲:

QUOTE:CREATE TRIGGER 觸發器名 ON 表名/視圖名

{ FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT] [,] [UPDATE] }

AS

觸發器內容(SQL 語句)

QUOTE:SQL SERVER 聯機叢書的描述:

AFTER

指定觸發器只有在觸發 SQL 語句中指定的所有操作都已成功執行後才激發。所有的引用級聯操作和約束檢查也必須成功完成後,才能執行此觸發器。

如果僅指定 FOR 關鍵字,則 AFTER 是默認設置。

不能在視圖上定義 AFTER 觸發器。

INSTEAD OF

指定執行觸發器而不是執行觸發 SQL 語句,從而替代觸發語句的操作。

在表或視圖上,每個 INSERT、UPDATE 或 DELETE 語句最多可以定義一個 INSTEAD OF 觸發器。然而,可以在每個具有 INSTEAD OF 觸發器的視圖上定義視圖。

INSTEAD OF 觸發器不能在 WITH CHECK OPTION 的可更新視圖上定義。如果向指定了 WITH CHECK OPTION 選項的可更新視圖添加 INSTEAD OF 觸發器,SQL Server 將産生一個錯誤。用戶必須用 ALTER VIEW 刪除該選項後才能定義 INSTEAD OF 觸發器。

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

是指定在表或視圖上執行哪些數據修改語句時將激活觸發器的關鍵字。必須至少指定一個選項。在觸發器定義中允許使用以任意順序組合的這些關鍵字。如果指定的選項多于一個,需用逗號分隔這些選項。

對于 INSTEAD OF 觸發器,不允許在具有 ON DELETE 級聯操作引用關系的表上使用 DELETE 選項。同樣,也不允許在具有 ON UPDATE 級聯操作引用關系的表上使用 UPDATE 選項。

現在根據上面的語法我們建立一個觸發器(注意一點,觸發器是附于一張表或視圖的,所以只能在表裏建立或在查詢分析器裏建立),這個觸發器的功能就是自動更新庫存數量

QUOTE:CREATE TRIGGER [trUpdateMaterielNum] ON [dbo].[uRecord]

-- 表明在插入記錄之後執行這個觸發器

AFTER INSERT

AS

-- 當前更新的編號

DECLARE @intID int

-- 當前更新的數量

DECLARE @intNum int

-- 當前模式

DECLARE @intMode int

-- 判斷是否有記錄錄被更新,@@ROWCOUNT是系統函數,返回受上一語句影響的行數。

IF @@ROWCOUNT >0

BEGIN

-- 取得當前插入的物品編號和數量,Inserted 表用于存儲 INSERT 和 UPDATE 語句所影響的行的副本。

SELECT @intID=mId,@intNum=rNum,@intMode=rMode FROM Inserted

-- 判斷當前模式(0爲入庫,1爲出庫)來更新當前物品的數量

IF @intMode = 0

UPDATE uMateriel SET mNum = mNum + @intNum WHERE mId=@intID

ELSE

UPDATE uMateriel SET mNum = mNum - @intNum WHERE mId=@intID

END

我們現在來了解一下這個觸發器,首先使用 CREATE TRIGGER 語句定義一個基于 uRecord 表的觸發器 trUpdateMaterielNum,AFTER INSERT 表明這個觸發器會在插入記錄之後執行,也就是說當我們在程序裏執行 INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0) 這條語句之後,trUpdateMaterielNum這個觸發器裏的內容就會被自動執行,也就是說庫存將會被自動更新了。現在我們更改一下ASP的代碼

QUOTE:On Error Resume Next

'// 設 adoConn 爲已經連接的 ADODB.Connection 對象

'// 插入物品入庫記錄

adoConn.Execute("INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)")

'// 判斷是否産生了錯誤

If Err.Number <> 0 Then

Response.Write "錯誤!"

Err.Clear

End If

是不是簡化了很多呢,是的,在這裏已經不用考慮庫存方面,只需要插入流水帳就可以了,庫存更新就交由觸發器來處理。

以上的例子是觸發器的其中一個應用,在觸發器的參數中還有 DELETE、UPDATE,他們分別在刪除和更新時或之後執行。下面看一個刪除時的觸發器例子。

我們在數據庫中增加一個表,用來記錄日志,其結構如下:

QUOTE:uSysLog

--------------

lId int

lEvent nvarchar(200)

lTime datetime DEFAULT GetDate()

現在假設這張表是用來記錄系統的日志用的,當我們刪除一條流水帳時,往日志表裏記錄一條事件,那麽我們來創建一個基于 uRecord 表的刪除時的觸發器

QUOTE:CREATE TRIGGER [trDeleteRecord] ON [dbo].[uRecord]

-- 表明在插入記錄之後執行這個觸發器

FOR DELETE

AS

-- 當前刪除的流水號

DECLARE @intID int

-- 當前刪除的數量

DECLARE @intNum int

-- 當前模式

DECLARE @intMode int

-- 判斷是否有記錄錄被更新,@@ROWCOUNT是系統函數,返回受上一語句影響的行數。

IF @@ROWCOUNT >0

BEGIN

-- 取得當前刪除的行信息,Deleted 表用于存儲 DELETE 和 UPDATE 語句所影響的行的複本。

SELECT @intID=rId,@intNum=rNum,@intMode=rMode FROM Deleted

-- 向日志表中插入一條簡單的刪除事件日志

INSERT INTO uSysLog (lEvent) VALUES ('用戶刪除了流水號爲:' + CAST(@intID as nvarchar(20) + ',數量:' + CAST(@intNum as nvarchar(20) + ',方向:' + CASE @intMode WHEN 0 THEN '入庫' ELSE '出庫' END)

END

建立好觸發器後,現在只要我們刪除 uRecord 表中的一條記錄,就會在系統日志中增加一條事件日志。

通過以上簡單的介紹,希望原來沒有使用過觸發器的朋友能對觸發器有個大致的概念和印象,如果你要深入了解的話,SQL SERVER聯機叢書就是你的好幫手。那麽觸發器的簡單應用就介紹到這兒了,我們下次再會。

· 把年齡相仿的獅虎熊放一起,誰更厲害?結果出人意料

很多人都想知道獅子、老虎和熊打起來誰最厲害,于是便有好事之人把這三種動物關在一起...

· 湖北宜昌三峽壩區水面驚現神秘動物

近日,湖北宜昌,一段視頻在當地熱傳:有網友在三峽壩區拍到神秘動物,體型碩大數米長...

· 什麽是語段?語段的類型以及和句群、段落的區別與聯系是什麽?

句群是最高級的語言單位。 段落(自然段)是章法單位...

 
SQL Server數據庫開發之觸發器的實際應用
說明:由于個人能力有限,文章中難免會出現錯誤或遺漏的地方,敬請諒解!同時歡迎你指出,以便我能及時修改,以免誤導下一個看官。最後希望本文能給你帶來一定的幫助。 不知道在壇子裏有多少朋友使用觸發器,如果你已...查看完整版>>SQL Server數據庫開發之觸發器的實際應用
 
SQL Server數據庫開發的二十一條軍規
如果你正在負責一個基于SQL Server的項目,或者你剛剛接觸SQL Server,你都有可能要面臨一些數據庫性能的問題,這篇文章會爲你提供一些有用的指導(其中大多數也可以用于其它的DBMS)。 在這裏,我不打算介紹使用SQL...查看完整版>>SQL Server數據庫開發的二十一條軍規
 
淺談實際開發中數據源在JDBC中的應用
  數據源在JDBC中的應用衆所周知,JDBC(Java數據庫連接)是Java2企業版的重要組成部分。它是基于SQL層的API。通過把SQL語句嵌入JDBC接口的方法中,用戶可以通過Java程序執行幾乎所有的數據庫操作。    JDBC只...查看完整版>>淺談實際開發中數據源在JDBC中的應用
 
淺談實際開發中數據源在JDBC中的應用
  數據源在JDBC中的應用衆所周知,JDBC(Java數據庫連接)是Java2企業版的重要組成部分。它是基于SQL層的API。通過把SQL語句嵌入JDBC接口的方法中,用戶可以通過Java程序執行幾乎所有的數據庫操作。  JDBC只提供...查看完整版>>淺談實際開發中數據源在JDBC中的應用
 
淺談實際開發中數據源在JDBC中的應用
數據源在JDBC中的應用衆所周知,JDBC(Java數據庫連接)是Java2企業版的重要組成部分。它是基于SQL層的API。通過把SQL語句嵌入JDBC接口的方法中,用戶可以通過Java程序執行幾乎所有的數據庫操作。JDBC只提供了接口,...查看完整版>>淺談實際開發中數據源在JDBC中的應用