| 導購 | 订阅 | 在线投稿
分享
 
 
當前位置: 王朝網路 >> mssql >> SQL Server數據庫開發之觸發器的實際應用
 

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

2008-07-11 05:57:43  編輯來源:互聯網  简体版  手機版  評論  字體: ||
 
 
  說明:由于個人能力有限,文章中難免會出現錯誤或遺漏的地方,敬請諒解!同時歡迎你指出,以便我能及時修改,以免誤導下一個看官。最後希望本文能給你帶來一定的幫助。

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

  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聯機叢書就是你的好幫手。那麽觸發器的簡單應用就介紹到這兒了,我們下次再會。
 
 
 
上一篇《講解Microsoft SQL Server的行式觸發器》
下一篇《Microsoft SQL Server 2008數據庫的新標志》
 
 
 
日版寵物情人插曲《Winding Road》歌詞

日版寵物情人2017的插曲,很帶節奏感,日語的,女生唱的。 最後聽見是在第8集的時候女主手割傷了,然後男主用嘴幫她吸了一下,插曲就出來了。 歌手:Def...

兄弟共妻,我成了他們夜裏的美食

老鍾家的兩個兒子很特別,就是跟其他的人不太一樣,魔一般的執著。兄弟倆都到了要結婚的年齡了,不管自家老爹怎麽磨破嘴皮子,兄弟倆說不娶就不娶,老父母爲兄弟兩操碎了心...

如何磨出破洞牛仔褲?牛仔褲怎麽剪破洞?

把牛仔褲磨出有線的破洞 1、具體工具就是磨腳石,下面墊一個硬物,然後用磨腳石一直磨一直磨,到把那塊磨薄了,用手撕開就好了。出來的洞啊很自然的。需要貓須的話調幾...

我就是掃描下圖得到了敬業福和愛國福

先來看下敬業福和愛國福 今年春節,支付寶再次推出了“五福紅包”活動,表示要“把欠大家的敬業福都還給大家”。 今天該活動正式啓動,和去年一樣,需要收集“五福”...

冰箱異味産生的原因和臭味去除的方法

有時候我們打開冰箱就會聞到一股異味,冰箱裏的這種異味是因爲一些物質發出的氣味的混合體,聞起來讓人惡心。 産生這些異味的主要原因有以下幾點。 1、很多人有這種習...

《極品家丁》1-31集大結局分集劇情介紹

簡介 《極品家丁》講述了現代白領林晚榮無意回到古代金陵,並追隨蕭二小姐化名“林三”進入蕭府,不料卻陰差陽錯上演了一出低級家丁拼搏上位的“林三升職記”。...

李溪芮《極品家丁》片尾曲《你就是我最愛的寶寶》歌詞

你就是我最愛的寶寶 - 李溪芮 (電視劇《極品家丁》片尾曲) 作詞:常馨內 作曲:常馨內 你的眉 又鬼馬的挑 你的嘴 又壞壞的笑 上一秒吵鬧 下...

烏梅的功效與作用以及烏梅的食用禁忌有哪些?

烏梅,又稱春梅,中醫認爲,烏梅味酸,性溫,無毒,具有安心、除熱、下氣、祛痰、止渴調中、殺蟲的功效,治肢體痛、肺痨病。烏梅泡水喝能治傷寒煩熱、止吐瀉,與幹姜一起制...

什麽是脂肪粒?如何消除臉部脂肪粒?

什麽是脂肪粒 在我們的臉上總會長一個個像脂肪的小顆粒,弄也弄不掉,而且顔色還是白白的。它既不是粉刺也不是其他的任何痘痘,它就是脂肪粒。 脂肪粒雖然也是由油脂...

網絡安全治理:國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰受害者

來源:中國青年報 新的攻擊方法不斷湧現,黑客幾乎永遠占據網絡攻擊的上風,我們不可能通過技術手段杜絕網絡攻擊。國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰...

 
 
 
說明:由于個人能力有限,文章中難免會出現錯誤或遺漏的地方,敬請諒解!同時歡迎你指出,以便我能及時修改,以免誤導下一個看官。最後希望本文能給你帶來一定的幫助。 不知道在壇子裏有多少朋友使用觸發器,如果你已經對觸發器很了解了,那麽請跳過此文,如果你還沒有使用過觸發器的話,那就讓我們來認識一下吧。 QUOTE: 定義: 觸發器是一種特殊類型的存儲過程,不由用戶直接調用。當使用下面的一種或多種數據修改操作在指定表中對數據進行修改時,觸發器會生效:UPDATE、INSERT 或 DELETE。觸發器可以查詢其它表,而且可以包含複雜的 SQL 語句。它們主要用于強制複雜的業務規則或要求。 觸發器一個應用就是保持和維護數據的完整性及合法性,那麽怎麽來理解呢?就是說你可以在程序裏提交任意數據,然後由觸發器來判斷數據的完整性及合法性,當然這裏只是舉例說明,實際應用中不推薦這樣用,應該由應用程序來驗證數據的完整性及合法性。 下面我還是以實例的方式來描述觸發器的應用。 假設:當前數據庫中有「uMateriel」和「uRecord」兩張表,他們分別用來保存物品信息和物品的出入庫記錄信息,結構如下 QUOTE: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,那麽通常我們需要做以下兩個步驟: 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聯機叢書就是你的好幫手。那麽觸發器的簡單應用就介紹到這兒了,我們下次再會。
󰈣󰈤
 
 
 
  免責聲明:本文僅代表作者個人觀點,與王朝網路無關。王朝網路登載此文出於傳遞更多信息之目的,並不意味著贊同其觀點或證實其描述,其原創性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容、文字的真實性、完整性、及時性本站不作任何保證或承諾,請讀者僅作參考,並請自行核實相關內容。
 
 
成熟風韻的Meinv
甜美少女
此女子姿色過人也
愛恨情愁傷哀怨
痞子的甘南日記
疑是銀河落九天
雪域壩上四——純美色
冬日戀歌——西城楊柳弄輕柔
 
>>返回首頁<<
 熱帖排行
 
 
王朝网络微信公众号
微信扫码关注本站公众号 wangchaonetcn
 
© 2005- 王朝網路 版權所有