SqlServer觸發器詳解_MsSql

     觸發器（trigger）是SQL server 提供給程式員和資料分析員來保證資料完整性的一種方法，它是與表事件相關的特殊的預存程序，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發，比如當對一個表進行操作（ insert，delete， update）時就會啟用它執行。

     觸發器經常用於加強資料的完整性條件約束和商務規則等。 觸發器可以從 DBA_TRIGGERS ，USER_TRIGGERS 資料字典中查到。SQL3的觸發器是一個能由系統自動執行對資料庫修改的語句。

      觸發器可以查詢其他表，而且可以包含複雜的SQL語句。它們主要用於強制服從複雜的商務規則或要求。例如：您可以根據客戶當前的帳戶狀態，控制是否允許插入新訂單。

     觸發器也可用於強制參考完整性，以便在多個表中添加、更新或刪除行時，保留在這些表之間所定義的關係。然而，強制參考完整性的最好方法是在相關表中定義主鍵和外鍵約束。如果使用資料庫圖表，則可以在表之間建立關係以自動建立外鍵約束。

     觸發器與預存程序的唯一區別是觸發器不能執行EXECUTE語句調用，而是在使用者執行Transact-SQL語句時自動觸發執行。

     查詢資料庫中所有觸發器：

select * from sysobjects where xtype='TR'

1、文法

create trigger [shema_name . ] trg_nameon { table | view }[ with encryption ]{ for | after | instead of }{ insert , update , delete }assql_statement

insert觸發器執行個體

create trigger teston alfor insertasdeclare @id int,@uid int,@lid int,@result charselect @id=id,@uid=uid,@lid=lid,@result=result from insertedif(@lid=4)begin update al set uid=99 where id=@id print 'lid=4時自動修改使用者id為99'end

update觸發器執行個體

create trigger test_updateon al for updateas declare @oldid int,@olduid int,@oldlid int,@newid int,@newuid int,@newlid int select @oldid=id,@olduid=uid,@oldlid=lid from deleted; select @newid=id,@newuid=uid,@newlid=lid from inserted if(@newlid>@oldlid) begin print 'newlid>oldid' rollback tran; end else print '修改成功'

delete觸發器執行個體

create trigger test_deleteon alfor deleteasdeclare @did int,@duid int,@dlid intselect @did=id,@duid=uid,@dlid=lid from deletedif(exists(select * from list where @dlid=id))beginprint '無法刪除'rollback tran;endelseprint '刪除成功'

圖文介紹觸發器

資料庫運行環境SqlServer2005

觸發器（trigger）是個特殊的預存程序，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發，當對一個表進行操作（ insert，delete， update）時就會啟用它執行，觸發器經常用於加強資料的完整性條件約束和商務規則等。其實往簡單了說，就是觸發器就是一個開關，負責燈的亮與滅，你動了，它就亮了，就這個意思。

觸發器的分類

1 DML（ 資料操縱語言 Data Manipulation Language）觸發器：是指觸發器在資料庫中發生DML事件時將啟用。DML事件即指在表或視圖中修改資料的insert、update、delete語句。

2 DDL（資料定義語言 (Data Definition Language) Data Definition Language）觸發器：是指當伺服器或資料庫中發生(DDL事件時將啟用。DDL事件即指在表或索引中的create、alter、drop語句也。

3 登陸觸發器：是指當使用者登入SQL SERVER執行個體建立會話時觸發。

DML觸發器介紹

1 在SQL SERVER 2008中，DML觸發器的實現使用兩個邏輯表DELETED和INSERTED。這兩個表是建立在資料庫伺服器的記憶體中，我們只有唯讀許可權。DELETED和INSERED表的結構和觸發器所在的資料表的結構是一樣的。當觸發器執行完成後，它們也就會被自動刪除：INSERED表用於存放你在操件insert、update、delete語句後，更新的記錄。比如你插入一條資料，那麼就會把這條記錄插入到INSERTED表：DELETED表用於存放你在操作 insert、update、delete語句前，你建立觸發器表中資料庫。

2 觸發器可通過資料庫中的相關表實現級聯更改，可以強制比用CHECK約束定義的約束更為複雜的約束。與 CHECK 條件約束不同，觸發器可以引用其它表中的列，例如觸發器可以使用另一個表中的 SELECT 比較插入或更新的資料，以及執行其它操作。觸發器也可以根據資料修改前後的表狀態，再行採取對策。一個表中的多個同類觸發器（INSERT、UPDATE 或 DELETE）允許採取多個不同的對策以響應同一個修改語句。

3 與此同時，雖然觸發器功能強大，輕鬆可靠地實現許多複雜的功能，為什麼又要慎用？過多觸發器會造成資料庫及應用程式的維護困難，同時對觸發器過分的依賴，勢必影響資料庫的結構，同時增加了維護的複雜程式。

觸發器步驟詳解

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

create trigger mytrigger on AddTable
for update

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

3 接下來我們來將AddTable表中的資料執行一個更改的操作：

4 執行後，我們會發現，觸發器被觸發，輸出了我們設定好的文本：

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

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

7 如果我想查詢當前資料庫中有多少觸發器，以方便我進行資料庫維護，只需要運行：

select * from sysobjects where xtype='TR'

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

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

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

9 那觸發器的功能雖大，但是一旦觸發，恢複起來就比較麻煩了，那我們就需要對資料進行保護，這裡就需要用到rollback資料復原~

10 第九步的意思就是查詢AddTable表，如果裡面存在TableName=newTable的，資料就復原，觸發器中止，那我們再進行一下測試，對AddTable表變更，發現，觸發update觸發器之後，因為有資料保護，觸發器中止：

注意事項

禁用和開啟觸發器都需要一定的許可權，如果許可權不夠是無法進行操作的。

注意運行後的錯誤提示，對於糾正錯誤是很有協助的。