qileilove

          blog已經轉移至github,大家請訪問 http://qaseven.github.io/

          SQL Server2005中觸發器的運用

            編寫過存儲過程的人,再編寫觸發器時會發現:他們的語法、格式是非常類似的。其實觸發器就是一種特殊類型的存儲過程。他們都是預編譯的,在程序正式編譯前就由編譯器進行編譯,存儲在服務器端。

            不過,觸發器與一般的存儲過程也有些區別。觸發器主要是通過對數據庫的增刪改的操作,或者是一個觸發動作的觸發作用等事件觸發而被執行;而存儲過程則是通過像傳遞SQL語句一樣,傳遞存儲過程的名字來被程序調用,實現功能。

            觸發器一共分為五種類型:Update觸發器,Insert觸發器、Delete觸發器、Instead of觸發器和After觸發器。前三個分別是相應表上進行更新、插入、刪除操作時觸發。Instead of觸發器在不執行插入、更新或刪除操作時觸發。

            在觸發器中存在兩個虛擬表:Inserted表和Deleted表。Inserted表保存的是Insert或Update之后所影響的記錄形成的表,Deleted保存的是Delete或update之前所影響的記錄形成的表。這兩個表是邏輯表,這兩個表是動態駐留在內存中的,當觸發器工作完成,這兩個表也被刪除。

            觸發器的創建代碼格式:

          CREATE TRIGGER trigger_name      --觸發器的名字
          ON table|view [WITH ENCRYPTION]  --在哪個表上創建觸發器
          { FOR | AFTER | INSTEAD OF }    
          {[INSERT][,][UPDATE][,][DELETE]} --激活觸發器的類型
          AS sql_statements […n]

            代碼中關鍵字for、after、Insteadof分別代表不同的使用范圍:

            for表示為AFTER觸發器,且該類型觸發器僅能在表上創建;

            after表示只有在執行了指定的操作INSERT、DELETE、UPDATE之后觸發器才被激活,執行觸發器中的SQL語句;

            instead of當為表或視圖定義了針對某一操作INSERT、DELETE、UPDATE的INSTEAD OF 類型觸發器,且執行了相應的操作時,盡管觸發器被觸發,但相應的操作并不被執行,而運行的僅是觸發器SQL語句本身。

            下面說下觸發器的作用:

            1、級聯修改數據庫中的相關的表;

            看下面的牛腩新聞發布系統的例子:其中一個新聞類別(Category)對應多個或者0條新聞;一條新聞(news)對應著多個或者0個新聞評論(comment)。

          set ANSI_NULLS ON
          set QUOTED_IDENTIFIER ON
          go
          -- =============================================
          -- Author:  劉正權
          -- Create date: 2008-11-15 11:13
          -- Description: 刪除新聞類別觸發器
          -- =============================================
          CREATE TRIGGER trigCategoryDelete
             ON  category
             instead of DELETE
          AS
          BEGIN
          --刪除新聞,再在類別表中刪除--觸發器實現
           declare @caId int
           select @caId=id from deleted
           --刪除評論(選出多條用in,一條用=號)
           delete comment where newsId in (select newsId from news wherecaId=@caId)
           --刪除新聞
           delete news wherecaId=@caId
           --刪除類別
           delete category whereid=@caId
          END

            2、執行比核查約束更為復雜的約束操作;在觸發器中可以書寫更為復雜的SQL語句,例如可以引用多個表,并使用if……else等語句做更復雜的檢查。

            下面看下例子:如果更改了學生的學號,則他的借書記錄表中記錄也同時更新。

            這時候,我們可以建立一下觸發器:

          SET ANSI_NULLS ON
          GO
          SET QUOTED_IDENTIFIER ON
          GO
          -- =============================================
          -- Author:  劉正權
          -- Create date:2012-4-22
          -- Description: 更改了學生的學號,則他的借書記錄表中記錄也同時更新
          -- =============================================
          Create Trigger truStudent
          On Student        --在Student表中創建觸發器
          for Update        --為什么事件觸發
          As                --事件觸發后所要做的事情
          if Update(StudentID)           
          begin

          Update BorrowRecord
          Set StudentID=i.StudentID
          From BorrowRecord br , Deleted   d ,Inserted i  --Deleted和Inserted臨時表
          Where br.StudentID=d.StudentID
          end 





            3、拒絕或回滾違反引用完整性的操作。檢查對數據庫的操作是否違反引用完整性,并選擇相應的操作;

            看下面的例子:不允許刪除任何銷售記錄大于等于20條的商店。

          SET ANSI_NULLS ON
          GO
          SET QUOTED_IDENTIFIER ON
          GO
          -- =============================================
          -- Author:  劉正權
          -- Create date: 2012-4-22
          -- Description: 不允許刪除任何銷售記錄大于等于20條的商店
          -- =============================================
          CREATE TRIGGER trDelSales
             ON  tblSales  
             for Delete
          AS 
           if(select Count(*) from Deleted 
           where Deleted.qty>=20)>0
          BEGIN
           print'您不能刪除任何記錄'
           rollback transaction   --事務回滾
          END
          GO

            4、比較表修改前后數據之間的差別,并根據差別才去相應的操作。

            例如:若想規定每次工資的變動幅度不能超過40%,使用觸發器可以將修改后的表數據和修改前的表數據進行比較,若超出40%,可以回滾該修改操作。

            觸發器是自動觸發的,一旦對表中的數據做了修改,該觸發器將立即被激活,充分體現了觸發的優勢,保持了數據的完整性;然而,觸發器性能通常是比較低的。

            當運行觸發器時,系統處理的大部分時間花費在參照它表達的這一處理上,因為這些表達既不在內存中,也不在數據庫設備上,而邏輯表(刪除表和插入表)總是位于內存中。所以觸發器參照的其他表的位置決定了操作花費時間的長短。

          posted on 2012-06-21 10:00 順其自然EVO 閱讀(2764) 評論(2)  編輯  收藏 所屬分類: 數據庫

          評論

          # re: SQL Server2005中觸發器的運用 2013-08-12 15:48 yeungll

          good  回復  更多評論   

          # re: SQL Server2005中觸發器的運用 2013-08-12 15:49 yeungll

          可以  回復  更多評論   

          <2012年6月>
          272829303112
          3456789
          10111213141516
          17181920212223
          24252627282930
          1234567

          導航

          統計

          常用鏈接

          留言簿(55)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          搜索

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 泰兴市| 应城市| 景洪市| 民勤县| 东丰县| 永德县| 西平县| 六盘水市| 土默特右旗| 新晃| 普陀区| 衡山县| 类乌齐县| 河东区| 内丘县| 清苑县| 金秀| 新平| 柏乡县| 读书| 名山县| 枝江市| 祁东县| 黑山县| 西宁市| 祁阳县| 突泉县| 双江| 贵港市| 宁晋县| 江津市| 南投县| 新蔡县| 定结县| 哈尔滨市| 咸宁市| 云南省| 张家川| 香港| 岱山县| 桂东县|