SQL Server 觸發器

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

觸發器

轉自：http://www.cnblogs.com/chenmh

概述

   觸發器為特殊類型的預存程序，可在執行語言事件時自動生效。SQL Server 包括三種常規類型的觸發器：DML 觸發程序、DDL 觸發程序和登入觸發器。

當伺服器或資料庫中發生資料定義語言 (Data Definition Language) (DDL) 事件時將調用 DDL 觸發程序。登入觸發器將為響應 LOGON 事件而激發預存程序。與 SQL Server 執行個體建立使用者會話時將引發此事件。

當資料庫中發生資料操作語言 (DML) 事件時將調用 DML 觸發程序。DML 事件包括在指定表或視圖中修改資料的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發程序可以查詢其他表，還可以包含複雜的 Transact-SQL 陳述式。將觸發器和觸發它的語句作為可在觸發器內復原的單個事務對待。如果檢測到錯誤（例如，磁碟空間不足），則整個事務即自動復原。

 

步驟

   本文主要講述DML觸發器，DML觸發器有兩種：AFTER，INSTEAD OF觸發器，同時DML 觸發程序使用 deleted 和 inserted 邏輯（概念）表。 它們在結構上類似於定義了觸發器的表，即對其嘗試執行了使用者操作的表。 在 deleted 和 inserted 表儲存了可能會被使用者更改的行的舊值或新值。

• 對於INSERT 操作，inserted保留新增的記錄，deleted無記錄
• 對於DELETE 操作，inserted無記錄，deleted保留被刪除的記錄
• 對於UPDATE操作，inserted保留修改後的記錄，deleted保留修改前的記錄

    一.文法

CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view }  [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }

 

     二.建立表

CREATE TABLE Class(Cno INT PRIMARY KEY,Cname nvarchar(20) not null)goCREATE TABLE Student(SNO INT PRIMARY KEY IDENTITY(1,1),Sname CHAR(10) not null,Age int not null,Sex char(2) not null,Cno int NOT NULL)ALTER TABLE Student ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class(Cno)go

AFTER觸發器

AFTER 指定 DML 觸發程序僅在觸發 SQL 陳述式中指定的所有操作都已成功執行時才被觸發。 所有的引用級聯操作和約束檢查也必須在激發此觸發器之前成功完成。

如果僅指定 FOR 關鍵字，則 AFTER 為預設值。

不能對視圖定義 AFTER 觸發器

• insert觸發

當向Class表中插入一條資料時，擷取插入的cno，同時向Student表中插入一條資料

IF OBJECT_ID(‘TR_Class_insert‘,‘TR‘) IS NOT NULL
DROP TRIGGER TR_Class_insert
G0
CREATE TRIGGER TR_Class_insert on ClassAFTER INSERTASBEGIN  DECLARE @Cno INT  SELECT @Cno=Cno FROM inserted----擷取插入的資料CNO  INSERT INTO Student(Sname,Age,Sex,Cno)  VALUES(‘李明‘,20,‘男‘,@Cno)END goINSERT INTO ClassSELECT 101,‘一班‘SELECT * FROM ClassSELECT * FROM Student

• UPDATE觸發

擷取修改的Age值，如果Age為負數則執行復原操作，否則輸出修改前後的Age值

IF OBJECT_ID(‘TR_Student_update‘,‘TR‘) IS NOT NULLDROP TRIGGER TR_Student_updateGOCREATE TRIGGER TR_Student_update on StudentAFTER UPDATEASBEGIN   DECLARE @Age_old int,@Age_new int   SELECT @Age_old=Age from deleted ----擷取修改前的   SELECT @Age_new=Age FROM inserted----擷取更改後的資料   if @Age_new<0   begin   print ‘年齡不能為負數‘   rollback;   end   else    BEGIN   print @Age_old   print @Age_new   ENDENDgoupdate Studentset Age=-20where SNO=1SELECT * FROM ClassSELECT * FROM Studentupdate Studentset Age=25where SNO=1SELECT * FROM ClassSELECT * FROM Student

•  delete觸發

擷取被刪除的資料，返回錯誤提示，該步驟正好驗證了“所有的引用級聯操作和約束檢查也必須在激發此觸發器之前成功完成”，該步驟不會返回制定的錯誤提示，因為被刪除的資料作用於外鍵約束，所以先於觸發器操作執行外鍵約束，返回約束錯誤提示，並執行復原.

IF OBJECT_ID(‘TR_Class_delete‘,‘TR‘) IS NOT NULLDROP TRIGGER TR_Class_deleteGOCREATE TRIGGER TR_Class_delete on ClassAFTER DELETEASBEGIN  DECLARE @Cno int  SELECT @Cno=Cno from DELETED---擷取被刪除的記錄  IF @Cno>0begin  RAISERROR (‘資料不能被刪除，被用於外鍵約束‘, 16, 10);  rollback----執行復原操作  endENDSELECT * FROM ClassSELECT * FROM StudentDELETE FROM Classwhere CNO=101SELECT * FROM ClassSELECT * FROM Student

對Student表建立外鍵約束，用於級聯操作 ON DELETE，對於表的串聯刪除更新操作這裡就不講述了
刪除之前建立的外鍵約束,並建立具有串聯更新刪除操作的外鍵約束

alter table studentdrop constraint FK_SNO_CnoALTER TABLE StudentADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno)ON DELETE CASCADE ON UPDATE CASCADE

再執行刪除語句，返回制定錯誤提示“資料不能被刪除，被用於外鍵約束”並執行復原操作

DELETE FROM Classwhere CNO=101SELECT * FROM ClassSELECT * FROM Student

 

INSTEAD OF觸發器

指定執行 DML 觸發程序而不是觸發 SQL 陳述式，因此，其優先順序高於觸發語句的操作。 不能為 DDL 或登入觸發器指定 INSTEAD OF。

對於表或視圖，每個 INSERT、UPDATE 或 DELETE 語句最多可定義一個 INSTEAD OF 觸發器。 但是，可以為具有自己的 INSTEAD OF 觸發器的多個視圖定義視圖。

INSTEAD OF 觸發器不可以用於使用 WITH CHECK OPTION 的可更新視圖。 如果將 INSTEAD OF 觸發器添加到指定了 WITH CHECK OPTION 的可更新視圖中，則 SQL Server 將引發錯誤。 使用者須用 ALTER VIEW 刪除該選項後才能定義 INSTEAD OF 觸發器

對於 INSTEAD OF 觸發器，不允許對具有指定級聯操作 ON DELETE 的參考關聯性的表使用 DELETE 選項。 同樣，也不允許對具有指定級聯操作 ON UPDATE 的參考關聯性的表使用 UPDATE 選項

•  Insert 觸發

-------insert 觸發----刪除已有的instead of觸發器declare @name nvarchar(100)select @name=name from sys.triggers where  object_name(parent_id)=‘student‘ and is_instead_of_trigger=1set @name=‘drop trigger ‘[email protected]exec (@name)IF OBJECT_ID(‘TR_Student_instead_insert‘,‘TR‘) IS NOT NULLDROP TRIGGER TR_Student_instead_insertGOCREATE TRIGGER TR_Student_instead_insert on StudentINSTEAD OF insertASBEGIN SELECT * into T_back from inserted ----擷取即將插入的資料 ENDselect * from Studentselect * from ClassINSERT INTO Student(Sname,Age,Sex,Cno)values(‘張三‘,23,‘男‘,102)select * from T_back

 

•  delete觸發

建立觸發器失敗，因為之前建立外鍵約束時添加了on delete cascade

IF OBJECT_ID(‘TR_Student_instead_delete‘,‘TR‘) IS NOT NULLDROP TRIGGER TR_Student_instead_deleteGOCREATE TRIGGER TR_Student_instead_delete on StudentINSTEAD OF DELETEASBEGIN  DECLARE @Cno int  SELECT @Cno=Cno from DELETED---擷取被刪除的記錄  IF EXISTS (SELECT * FROM Class where [email protected])   begin  rollback----執行復原操作  RAISERROR (‘資料不能被刪除，被用於外鍵約束1‘, 16, 10);  endEND訊息 2113，層級 16，狀態 1，過程 TR_Student_instead_delete，第 10 行因為表 ‘Student‘ 的 FOREIGN KEY 使用級聯 DELETE 或 UPDATE，所以無法對該表 建立 INSTEAD OF DELETE 或 INSTEAD OF UPDATE TRIGGER ‘TR_Student_instead_delete‘。

重建外鍵約束，刪除級聯

alter table studentdrop constraint FK_SNO_CnoALTER TABLE StudentADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno)

 

•  UPDATE觸發

-----同一張表中只能定義一個instead of 觸發器，刪除表之前建立的instead of 觸發declare @name nvarchar(100)select @name=name from sys.triggers where  object_name(parent_id)=‘student‘ and is_instead_of_trigger=1set @name=‘drop trigger ‘[email protected]exec (@name)IF OBJECT_ID(‘TR_Student_instead_update‘,‘TR‘) IS NOT NULLDROP TRIGGER TR_Student_instead_updateGOCREATE TRIGGER TR_Student_instead_update on StudentINSTEAD OF updateASBEGIN  DECLARE @Age_del int ,@Age_up int  SELECT @Age_del=Age from DELETED---擷取被更改的記錄  SELECT @Age_up=Age from Inserted  begin  print @Age_del  print @Age_up  select * from Student ----查詢資料是否被更改  endEND----查詢更新前的表資料select * from student SNO    Sname    Age    Sex    Cno13    李明          22    男    101update Studentset age=-2where CNO=101

----對於前面定義的after觸發器age不能為負數也不會執行，instead of 觸發器高於執行語句，高於after 觸發
SNO    Sname    Age    Sex    Cno13    李明          22    男    101select * from student SNO    Sname    Age    Sex    Cno13    李明          22    男    101(1 行受影響)22-2(1 行受影響)(1 行受影響)當表上面定義了instead of 觸發器，指定執行 DML 觸發程序而不是觸發 SQL 陳述式，因此，其優先順序高於觸發語句的操作，而且也不會執行表上面定義的after觸發器

 

 建立帶欄位判斷的觸發器，根據對特定列的 UPDATE 或 INSERT 修改來執行某些操作

------建立欄位更新判斷的update觸發器ALTER TABLE ClassADD Address nvarchar(50)IF OBJECT_ID(‘TR_Class_Update‘,‘TR‘) IS NOT NULLDROP TRIGGER TR_Class_UpdateGOCREATE TRIGGER TR_Class_Update on ClassAFTER UPDATEASBEGIN  IF UPDATE(Cname) or UPDATE(Address)  BEGIN  RAISERROR (‘資料不能被修改‘, 16, 10)  ROLLBACK  ENDENDSELECT * FROM ClassUPDATE Classset Address=‘5棟101‘where Cno=101SELECT * FROM Class

 

總結

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

SQL Server 觸發器