sql server 觸發器

標籤：style   blog   http   io   ar   os   使用   for   sp   

原文:sql server 觸發器

             本文是學習使用觸發器資料的總結，內容來自網路，在文章結尾處會給出相應的連結地址。

            在SQL server 2008 R2中，既支援T-SQL的觸發器，也支援建立CLR觸發器。

          一 觸發器簡介

　　　　　　1）觸發器分類

• 　　　　　　　　登陸觸發器：是指當使用者登入SQL SERVER執行個體建立會話時觸發。
• 　　　　　　　　DDL（資料定義語言 (Data Definition Language) Data Definition Language）觸發器：是指當伺服器或資料庫中發生(DDL事件時將啟用。DDL事件即指在表或索引中的create、alter、drop語句也。
• 　　　　　　　　DML（ 資料操縱語言 Data Manipulation Language）觸發器：是指觸發器在資料庫中發生DML事件時將啟用。DML事件即指在表或視圖中修改資料的insert、update、delete語句

                 2）DML觸發器介紹

•               　　在SQL SERVER 2008中，DML觸發器的實現使用兩個邏輯表DELETED和INSERTED。這兩個表是建立在資料庫伺服器的記憶體中，我們只有唯讀許可權。 DELETED和INSERED表的結構和觸發器所在的資料表的結構是一樣的。當觸發器執行完成後，它們也就會被自動刪除：INSERED表用於存放你在 操件insert、update、delete語句後，更新的記錄。比如你插入一條資料，那麼就會把這條記錄插入到INSERTED表：DELETED表 用於存放你在操作 insert、update、delete語句前，你建立觸發器表中資料庫。觸發器可通過資料庫中的相關表實現級聯更改，可以強制比用CHECK約束定義的約束更為複雜的約束。與 CHECK 條件約束不同，觸發器可以　　　　　　　　
• 　　　　　　引用其它表中的列，例如觸發器可以使用另一個表中的 SELECT 比較插入或更新的資料，以及執行其它操作。觸發器也可以根據資料修改前後的表狀態，再行採取對策。一個表中的多個同類觸發器（INSERT、UPDATE 或 DELETE）允許採取多個不同的對策以響應同一個修改語句　　
• 　　　　　　與此同時，雖然觸發器功能強大，輕鬆可靠地實現許多複雜的功能，為什麼又要慎用？過多觸發器會造成資料庫及應用程式的維護困難，同時對觸發器過分的依賴，勢必影響資料庫的結構，同時增加了維護的複雜程式。

　　 二 建立T-SQL觸發器小例子

　　　　

　　　　1）首先，我們來嘗試建立一個觸發器，要求就是在AddTable這個表上建立一個Update觸發器，語句為：

　　　　　　　　create trigger mytrigger on AddTable for update

           2）然後就是sql語句的部分了，主要是如果發生update以後，要求觸發器觸發一個什麼操作。這裡的意思就是如果出現update了，觸發器就會觸發輸出：the table was updated!。

                   語句為：create trigger mytrigger on AddTable for update as print ‘the table was updated‘

           3）接下來我們來將AddTable表中的資料執行一個更改的操作，語句為： update AddTable set ****

           4)  執行後，我們會發現，觸發器被觸發，輸出了我們設定好的文本（在下方的訊息框）

           5）那觸發器建立以後呢，它就正式開始工作了，這時候我們需要更改觸發器的話，只需要將開始的create建立變為alter，然後修改邏輯即可：alter trigger mytrigger on AddTable for update

            6)如果我們想查看某一個觸發器的內容，直接運行：exec sp_helptext [觸發器名]

            7)如果我想查詢當前資料庫中有多少觸發器，以方便我進行資料庫維護，只需要運行： select * from sysobjects where xtype=‘TR‘

            8)我們如果需要關閉或者開啟觸發器的話，只需要運行：

　　　　　　　　disable trigger [觸發器名] on database --禁用觸發器

　　　　　　　　enable trigger [觸發器名] on database --開啟觸發器 

                原文： http://jingyan.baidu.com/article/77b8dc7f2b82416175eab65b.html

           三 CLR 觸發程序

                    MSDN上講的很清楚 http://msdn.microsoft.com/zh-cn/library/vstudio/938d9dz2%28v=vs.100%29.aspx

• 　　開啟一個現有的“SQL CLR 資料庫專案”，或者建立一個新項目。 有關更多資訊，請參見如何：為使用 SQL Server 通用語言執行平台整合的資料庫物件建立項目。

• 　　在“項目”菜單上選擇“添加新項”。

• 　　在“添加新項”對話方塊中，選擇“觸發器”。

• 　　鍵入新觸發器的“名稱”。

• 　　添加觸發器執行時要啟動並執行代碼。 請參見此過程後面的第一個樣本。

• 　　在“方案總管”中開啟“TestScripts”檔案夾，並雙擊 Test.sql 檔案。

•      將代碼添加到 Test.sql 檔案中以執行觸發器。

•     按 F5 產生、部署並調試此觸發器。 有關如何不進行調試而直接部署的資訊，請參見如何：將 SQL CLR 資料庫專案項部署到 SQL Server。

•     查看所示的結果“輸出”視窗和選擇顯示輸出： 資料庫輸出。

             注意：SQL Server 2005 和 SQL Server 2008 只支援使用 .NET Framework 2.0、3.0 或 3.5 版產生的 SQL Server 項目。 如果您嘗試部署SQL Server項目，SQL Server 2005或SQL Server 2008，將顯示錯誤訊息： Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly ‘AssemblyName‘ failed because assembly ‘AssemblyName‘ failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database（在進行校正是您要部署的程式集的名稱）。 有關更多資訊，請參見如何：為使用 SQL Server 通用語言執行平台整合的資料庫物件建立項目。

             1）CLR觸發器代碼

　　　　此樣本示範以下這種情況：使用者選擇他們需要的任何使用者名稱，但是您希望知道哪些使用者輸入了電子郵件地址作為使用者名稱。 此觸發器檢測該資訊並將它記錄到審核表。

　　　　　　using System.Data.SqlClient;　　　　　　using System.Text.RegularExpressions;　　　　　　using Microsoft.SqlServer.Server;　　　　　　public partial class Triggers　　　　　　{    　　　　　　[SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")]   　　　　　　 public static void UserNameAudit()    　　　　　　{        　　　　　　SqlTriggerContext triggContext = SqlContext.TriggerContext;       　　　　　　 SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);        　　　　　　if (triggContext.TriggerAction == TriggerAction.Insert)        　　　　　　{            　　　　　　using (SqlConnection conn = new SqlConnection("context connection=true"))            　　　　　　　{                　　　　　　conn.Open();                　　　　　　SqlCommand sqlComm = new SqlCommand();                　　　　　　SqlPipe sqlP = SqlContext.Pipe;                　　　　　　sqlComm.Connection = conn;                　　　　　　sqlComm.CommandText = "SELECT UserName from INSERTED";                　　　　　　userName.Value = sqlComm.ExecuteScalar().ToString();                　　　　　　if (IsEMailAddress(userName.ToString()))                　　　　　　{                   　　　　　　 sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(userName)";                   　　　　　　 sqlP.Send(sqlComm.CommandText);                   　　　　　　 sqlP.ExecuteAndSend(sqlComm);                　　　　　　}           　　　　　　 }       　　　　　　 }    　　　　}    　　　　public static bool IsEMailAddress(string s)    　　　　{        　　　　return Regex.IsMatch(s, "^([\\w-]+\\.)*?[\\w-][email protected][\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");   　　　　}　　　　}

              2）測試指令碼

                   向位於項目的 TestScripts 檔案夾中的 Test.sql 檔案添加代碼以執行和測試觸發器。 例如，如果已部署了觸發器，您可以通過運行指令碼對其進行測試，該指令碼向設定了此觸發器的表中插入新行，從而可激發此觸發器。

              以下調試代碼假定存在具有以下定義的兩個表：

　　　　　　　　　　 CREATE TABLE Users                   (                        UserName    NVARCHAR(200)    NOT NULL,                        Pass    NVARCHAR(200)    NOT NULL                   )                   CREATE TABLE UsersAudit                     (                        UserName    NVARCHAR(200)    NOT NULL                     )
             測試指令碼：

                -- Insert one user name that is not an e-mail address and one that is                 INSERT INTO Users(UserName, Pass) VALUES(N‘someone‘, N‘cnffjbeq‘)                 INSERT INTO Users(UserName, Pass) VALUES(N‘[email protected]‘, N‘cnffjbeq‘)                  -- check the Users and UsersAudit tables to see the results of the trigger                 select * from Users                 select * from UsersAudit
 
           如果在sqlserver 中執行CLR觸發器出錯，那麼需要使用SQL語句更改一下資料庫的配置：
          exec sp_configure ‘show advanced options‘, ‘1‘;　　　　go　　　　reconfigure;　　　　go　　　　exec sp_configure ‘clr enabled‘, ‘1‘
　　　　go
　　　　reconfigure;　　　　exec sp_configure ‘show advanced options‘, ‘1‘;　　　　go

　　　　四觸發器的管理和查看　　　　

                 原文：http://www.cnblogs.com/shineqiujuan/archive/2009/04/23/1442137.html

                1、通過可視化操作來管理和查看觸發器

　　　　　　在Microsoft SQL Server Management Studio中，選中某一資料庫的某一張表時，在“物件總管詳細”視窗中有“觸發器”項。

通過“觸發器”的右鍵菜單功能，我們可以建立觸發器。如果原來的表中已經存在了觸發器，通過雙擊“觸發器”項可以查看到具體的觸發器，在此處可以執行 修改、刪除等操作。

　　　　　　2、通過查詢分析器來管理和查看觸發器
　　　　　　　　1）查看錶中的觸發器類型:
　　　　　　　　sp_helptrigger:返回對當前資料庫的指定表定義的 DML 觸發程序的類型。sp_helptrigger 不能用於 DDL 觸發程序。
　　　　　　　　　　樣本: EXEC sp_helptrigger ‘表名‘

　　　　　　　　2）查看觸發器的有關資訊:
　　　　　　　　sp_help:報告有關資料庫物件（sys.sysobjects 相容視圖中列出的所有對象）、使用者定義資料類型或某種資料類型的資訊。
　　　　　　　　樣本: EXEC sp_help ‘觸發器名‘

　　　　　　3）顯示觸發器的定義:
　　　　　　　　sp_helptext:顯示規則、預設值、未加密的預存程序、使用者定義函數、觸發器或視圖的文本。
　　　　　　　　樣本: EXEC sp_helptext ‘觸發器名‘

　　　　　　4）查看當前庫中所有的觸發器:
　　　　　　　　查詢指令碼：SELECT * FROM Sysobjects WHERE xtype = ‘TR‘

　　　　　　5）查看當前庫中所有的觸發器和與之相對應的表：
　　　　　　查詢指令碼：SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type=‘TR

            

sql server 觸發器