SQL SERVER資料庫開發之觸發器的應用1

 轉載：http://bbs.51js.com/viewthread.php?tid=52487&fpage=2

 

【原創:資料庫】SQL SERVER資料庫開發之觸發器的應用

標　　題：SQL SERVER資料庫開發之觸發器的應用
作　　者：栽培者
日　　期：2006-01-10
說　　明：由於個人能力有限，文章中難免會出現錯誤或遺漏的地方，敬請諒解！同時歡迎你指出，以便我能及時修改，以免誤導下一個看官。最後希望本文能給你帶來一定的協助。

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

引用:定義：
　　觸發器是一種特殊類型的預存程序，不由使用者直接調用。當使用下面的一種或多種資料修改操作在指定表中對資料進行修改時，觸發器會生效：UPDATE、INSERT 或 DELETE。觸發器可以查詢其它表，而且可以包含複雜的 SQL 陳述式。它們主要用於強制複雜的商務規則或要求。

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

　　下面我還是以執行個體的方式來描述觸發器的應用。

　　設：當前資料庫中有“uMateriel”和“uRecord”兩張表，他們分別用來儲存物品資訊和物品的出入庫記錄資訊，結構如下

引用:uMateriel
----------------
mId　　　int
mName　　nvarchar(40)
mNum　　 int DEFAULT 0

uRecord
----------------
rId　　　int
mId　　　int
rNum　　 int
rDate　　datetime DEFAULT GetDate()
rMode　　bit DEFAULT 0

　　好了，資料表已經有了，現在看一下實際的應用。

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

引用:1、在 uRecord 記錄表中增加一條物品A的購入記錄：
　　INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)

2、更新 uMateriel 物品庫存表中物品A的數量：
　　UPDATE uMateriel SET mNum = mNum + 100 WHERE mId=1

　　也就是說代碼中要先後處理以上兩條語句，才能保證庫存的準確性，以ASP代碼為例：

引用: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

　　以上代碼可以更新一條入庫記錄了，但是我們今天要瞭解的是觸發器的應用，那麼要在觸發器裡寫什麼內容可以簡化以上代碼呢？下面來建立一個觸發器。

　　建立觸發器的文法很長，簡化為：

引用:CREATE TRIGGER 觸發器名 ON 表名/視圖名
{ FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT] [,] [UPDATE] }
AS
　　觸發器內容（SQL 陳述式）

引用: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 選項。

　　現在根據上面的文法我們建立一個觸發器（注意一點，觸發器是附於一張表或視圖的，所以只能在表裡建立或在查詢分析器裡建立），這個觸發器的功能就是自動更新庫存數量

引用: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的代碼

引用: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，他們分別在刪除和更新時或之後執行。下面看一個刪除時的觸發器例子。

　　我們在資料庫中增加一個表，用來記錄日誌，其結構如下

引用:uSysLog
--------------
lId                int
lEvent        nvarchar(200)
lTime                datetime DEFAULT GetDate()

　　現在假設這張表是用來記錄系統的日誌用的，當我們刪除一條流水帳時，往日誌表裡記錄一條事件，那麼我們來建立一個基於 uRecord 表的刪除時的觸發器

引用: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聯機叢書就是你的好幫手。那麼觸發器的簡單應用就介紹到這兒了，我們下次再會。