SQL Server 觸發器

標籤：

觸發器

        觸發器簡介：

          觸發器（trigger）是個特殊的預存程序，它的執行不是由程式調用，也不是手工啟動，而是由事件來觸發，當對一個表進行操作（  insert，delete， update）時就會啟用它執行，觸發器經常用於加強資料的完整性條件約束和商務規則等。在我看來觸發器實際上就是一個事件，就像C#中，點擊一個按鈕會觸發相應的操作。

        觸發器的分類：

        (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觸發器最為常用，根據DML觸發器觸發的方式不同又分為以下兩種情況：

      （1）AFTER觸發器：它是在執行INSERT、UPDATE、DELETE語句操作之後執行觸發器操作。它主要是用於記錄變更後的處理或檢查，一旦發生錯誤，可以用Rollback Transaction語句來復原本次扣件，不過不能對視圖定義AFTER觸發器。       （2）INSTEAD OF觸發器：它在執行INSERT、UPDATE、DELETE語句操作之前執行觸發器本身所定義的操作。而INSTEAD OF觸發器是可以定義在視圖上的。

        INSERTED和DELETED

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

        觸發器的優缺點：

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

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

文法

        建立觸發器

1 CREATE TRIGGER 觸發器名稱2 ON 表名3 { FOR | AFTER | INSTEAD OF } 4 { [ INSERT ] [ , ] [ DELETE ] [ , ] 5    [UPDATE ] }6 AS 7   SQL 陳述式 [ ... n ] 

        刪除觸發器：

 

1 DROP TRIGGER 觸發器名 [ , ... n ]

 

          修改觸發器：

 

1 ALTER TRIGGER 觸發器名稱2 ON 表名3 { FOR | AFTER | INSTEAD OF } 4 { [ INSERT ] [ , ] [ DELETE ] [ , ] 5    [UPDATE ] }6 AS 7   SQL 陳述式 [ ... n ] 

 

          開啟和禁用：

 

1 disable trigger trigDB on database --禁用觸發器2 enable trigger trigDB on database --開啟觸發器

 

          提醒和保護：

 

1 print ‘刪除了觸發器***‘ 2 raiserror(‘資料一致性驗證‘,16,1)3 rollback transaction 

 

樣本

 

    在S表建立UPDATE觸發器：

1 Create trigger tri_Updates2 on s3 for update 4 as 5 print ‘the table s was updated‘

 

    禁止刪除SC表中成績不及格學生的記錄：

1 CREATE TRIGGER tri_del_grade2   ON SC FOR DELETE3   AS4     IF EXISTS(SELECT * FROM DELETED    5           WHERE Grade < 60)6       ROLLBACK 

 

   禁止將SC表中不及格學生的成績改為及格：

 1 create trigger tri_update_grade 2 on sc for update 3 as  4   if update(grade) 5   if exists(select * from inserted,deleted 6   where inserted.sno=deleted.sno and inserted.grade>=60 and deleted.grade<60) 7   begin     8     print ‘不能將不及格的成績改為及格‘ 9   rollback10   end

 

SQL Server 觸發器