SQL Server的觸發器詳解

Posted on by

一、概念

觸發器是一種特殊類型的存儲過程，不由用戶直接調用。

創建觸發器時會對其進行定義，以便在對特定表或列作特定類型的數據修改時執行。

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

觸發器也可用於強制引用完整性，以便在多個表中添加、更新或刪除行時，保留在這些表之間所定義的關系。

二、使用觸發器優缺點

  • 觸發器可通過數據庫中的相關表實現級聯更改；通過級聯引用完整性約束可以更有效地執行這些更改。
  • 觸發器可以強制比用 CHECK 約束定義的約束更為復雜的約束。與 CHECK 約束不同，觸發器可以引用其它表中的列。例如，觸發器可以使用另一個表中的 SELECT 比較插入或更新的數據，以及執行其它操作，如修改數據或顯示用戶定義錯誤信息。
  • 觸發器還可以強制執行業務規則
  • 觸發器也可以評估數據修改前後的表狀態，並根據其差異采取對策。

盡管觸發器有很多優點，但是在實際的項目開發中，特別是OOP思想的深入，觸發器的弊端也逐漸突顯，主要：

  • 過多的觸發器使得數據邏輯變得復雜
  • 數據操作比較隱含，不易進行調整修改
  • 觸發器的功能逐漸在代碼邏輯或事務中替代實現，更符合OO思想。

使用觸發器需慎重。

三、語法

CREATE TRIGGER trigger_name 
ON {table_name | view_name} 
{FOR | After | Instead of } [ insert, update,delete ]
AS           
    sql_statement

四、觸發器類型

SQL Server 包括兩種常規類型的觸發器：數據操作語言 (DML) 觸發器和數據定義語言 (DDL) 觸發器。

當INSERT、UPDATE 或 DELETE 語句修改指定表或視圖中的數據時，可以使用 DML 觸發器。

DDL 觸發器激發存儲過程以響應各種 DDL 語句，這些語句主要以CREATE、ALTER 和 DROP 開頭。 DDL 觸發器可用於管理任務，例如審核和控制數據庫操作。

1、數據操作語言 (DML) 觸發器

通常說的觸發器就是DML觸發器。

DML 觸發器在 INSERT、UPDATE 和 DELETE 語句上操作，並且有助於在表或視圖中修改數據時強制業務規則，擴展數據完整性。

DML觸發器又分以下分類：

1、After觸發器

After觸發器要求隻有執行某一操作insert、update、delete之後觸發器才被觸發，且隻能定義在表上。

  • insert觸發器
  • update觸發器
  • delete觸發器

2、Instead of 觸發器

Instead of 觸發器表示並不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身。既可以在表上定義instead of觸發器，也可以在視圖上定義。

2、數據定義語言 (DDL) 觸發器

在SQL Server2005後又增加瞭DDL觸發器。

DDL 觸發器將激發存儲過程以響應事件。但與 DML 觸發器不同的是，它們不會為響應針對表或視圖的 UPDATE、INSERT 或 DELETE 語句而激發。相反，它們將為瞭響應各種數據定義語言 (DDL) 事件而激發。這些事件主要與以關鍵字 CREATE、ALTER 和 DROP 開頭的 Transact-SQL 語句對應。執行 DDL 式操作的系統存儲過程也可以激發 DDL 觸發器。

DDL 觸發器使用場合：

  • 要防止對數據庫架構進行某些更改。
  • 希望數據庫中發生某種情況以響應數據庫架構中的更改。
  • 要記錄數據庫架構中的更改或事件。

五、DML觸發器具體應用

在觸發器實際應用中，主要還是建立約束以及級聯更新。

inserted與deleted表

觸發器有兩個特殊的表：插入表（instered表）和刪除表（deleted表）。這兩張是邏輯表也是虛表。有系統在內存中創建者兩張表，不會存儲在數據庫中。而且兩張表的都是隻讀的，隻能讀取數據而不能修改數據。

這兩張表的結果總是與被改觸發器應用的表的結構相同。當觸發器完成工作後，這兩張表就會被刪除。Inserted表的數據是插入或是修改後的數據，而deleted表的數據是更新前的或是刪除的數據。

1、insert觸發器

原理：當觸發INSERT觸發器時，新的數據行就會被插入到觸發器表和inserted表中。inserted表是一個邏輯表，它包含瞭已經插入的數據行的一個副本。inserted表包含瞭INSERT語句中已記錄的插入動作。inserted表還允許引用由初始化INSERT語句而產生的日志數據。觸發器通過檢查inserted表來確定是否執行觸發器動作或如何執行它。inserted表中的行總是觸發器表中一行或多行的副本。

場景：增加學生信息時，要校驗其年齡，暫定其年齡必須大於18，否則新增失敗

作用：校驗約束

--觸發器新增：隻允許錄取18歲以上學生
IF OBJECT_ID (N'TRIGER_Students_Insert', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Students_Insert;
GO

CREATE TRIGGER TRIGER_Students_Insert
ON Students
FOR INSERT
AS
    declare @age int
    select @age=COUNT(Students.ID) FROM Students INNER JOIN inserted ON Students.ID =inserted.ID    
    PRINT @age
    if(@age<18)
    begin
        raiserror('學生年齡必須要大於18哦',16,8)
        rollback tran
    end

2、update觸發器

原理：可將UPDATE語句看成兩步操作：即捕獲數據前像(before image)的DELETE語句，和捕獲數據後像(after image)的INSERT語句。當在定義有觸發器的表上執行UPDATE語句時，原始行（前像）被移入到deleted表，更新行（後像）被移入到inserted表。

觸發器檢查deleted表和inserted表以及被更新的表，來確定是否更新瞭多行以及如何執行觸發器動作。

可以使用IF UPDATE語句定義一個監視指定列的數據更新的觸發器。這樣，就可以讓觸發器容易的隔離出特定列的活動。當它檢測到指定列已經更新時，觸發器就會進一步執行適當的動作，例如發出錯誤信息指出該列不能更新，或者根據新的更新的列值執行一系列的動作語句。

場景：專業信息ID修改，對應的學生信息中專業ID也相應進行修改

--更新觸發器：更新專業ID時,同時更新學生的專業信息
IF OBJECT_ID (N'TRIGER_Majors_Update', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Majors_Update;
GO

CREATE TRIGGER TRIGER_Majors_Update
ON Majors
FOR UPDATE
AS
    IF UPDATE(ID)
    UPDATE Students Set MajorID=inserted.ID
    FROM Students,deleted,inserted
    WHERE Students.MajorID = deleted.ID

3、delete觸發器

原理：當觸發DELETE觸發器後，從受影響的表中刪除的行將被放置到一個特殊的deleted表中。deleted表是一個邏輯表，它保留已被刪除數據行的一個副本。deleted表還允許引用由初始化DELETE語句產生的日志數據。

使用DELETE觸發器時，需要考慮以下的事項和原則：

  • 當某行被添加到deleted表中時，它就不再存在於數據庫表中；因此，deleted表和數據庫表沒有相同的行。
  • 創建deleted表時，空間是從內存中分配的。deleted表總是被存儲在高速緩存中。
  • 為DELETE動作定義的觸發器並不執行TRUNCATE TABLE語句，原因在於日志不記錄TRUNCATE TABLE語句。

場景：學校某選修課取消。

處理邏輯：在刪除課程的同時，需要刪除該課程的選課信息。

--刪除觸發器：刪除課程時,同時刪除該課程的選課信息
IF OBJECT_ID (N'TRIGER_Courses_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Delete;
GO

CREATE TRIGGER TRIGER_Courses_Delete
ON Courses
FOR DELETE
AS
    DELETE SC
    FROM SC,deleted     
    WHERE SC.CourseID = deleted.ID

4、Instead Of 觸發器

用Instead Of觸發器實現與實例3相同的功能，具體實現代碼如下：

--Instead Of觸發器：刪除課程時,同時刪除該課程的選課信息
IF OBJECT_ID (N'TRIGER_Courses_Instead_Delete', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_Courses_Instead_Delete;
GO

CREATE TRIGGER TRIGER_Courses_Instead_Delete
ON Courses
Instead Of DELETE
AS
    declare @courseId int
    --獲取要刪除的課程ID
    SELECT @courseId=ID FROM deleted
    --刪除選課信息
    DELETE FROM SC WHERE CourseID = @courseId
    --刪除課程信息
    DELETE FROM Courses WHERE ID=@courseId

六、觸發器相關操作

1、刪除觸發器

drop trigger 觸發器名稱

刪除多個觸發器：drop trigger 觸發器名稱,觸發器名稱

2、重命名觸發器

用查詢分析器重命名或

exec sp_rename 原名稱, 新名稱

sp_rename 是 SQL Server自帶的一個存儲過程，用於更改當前數據庫中用戶創建的對象的名稱，如表名、列表、索引名等。

3、查看數據庫中所有的觸發器

select * from sysobjects where xtype='TR'

sysobjects 保存著數據庫的對象，其中 xtype 為 TR 的記錄即為觸發器對象。在 name 一列，我們可以看到觸發器名稱。

4、sphelptext 查看觸發器內容

exec sp_helptext '觸發器名稱'

5、sp_helptrigger 用於查看觸發器的屬性

sp_helptrigger 有兩個參數：第一個參數為表名；第二個為觸發器類型，為 char(6) 類型，可以是 INSERT、UPDATE、DELETE，如果省略則顯示指定表中所有類型觸發器的屬性。

exec sp_helptrigger tbl

七、遞歸、嵌套觸發器

1、遞歸觸發器

遞歸分兩種，間接遞歸和直接遞歸。我們舉例解釋如下，假如有表1、表2名稱分別為 T1、T2，在 T1、T2 上分別有觸發器 G1、G2。

  • 間接遞歸：對 T1 操作從而觸發 G1，G1 對 T2 操作從而觸發 G2，G2 對 T1 操作從而再次觸發 G1…
  • 直接遞歸：對 T1 操作從而觸發 G1，G1 對 T1 操作從而再次觸發 G1…

2、嵌套觸發器

類似於間接遞歸，間接遞歸必然要形成一個環，而嵌套觸發器不一定要形成一個環，它可以 T1->T2->T3…這樣一直觸發下去，最多允許嵌套 32 層。

3、設置直接遞歸

默認情況下是禁止直接遞歸的，要設置為允許有兩種方法：

exec sp_dboption 'dbName', 'recursive triggers', true

也可以EM：數據庫上點右鍵->屬性->選項。

4、設置間接遞歸、嵌套

默認情況下是允許間接遞歸、嵌套的，要設置為禁止有兩種方法：

exec sp_configure 'nested triggers', 0 --第二個參數為 1 則為允許

也可以EM：註冊上點右鍵->屬性->服務器設置。

八、觸發器回滾

我們看到許多註冊系統在註冊後都不能更改用戶名，但這多半是由應用程序決定的， 如果直接打開數據庫表進行更改，同樣可以更改其用戶名，在觸發器中利用回滾就可以巧妙地實現無法更改用戶名。

create trigger tr
on 表名
for update
as
    if update(userName)
        rollback tran

關鍵在最後兩句，其解釋為：如果更新瞭 userName 列，就回滾事務。

九、禁用、啟用觸發器

禁用：

alter table 表名 disable trigger 觸發器名稱

啟用：

alter table 表名 enable trigger 觸發器名稱

如果有多個觸發器，則各個觸發器名稱之間用英文逗號隔開。

如果把“觸發器名稱”換成“ALL”，則表示禁用或啟用該表的全部觸發器。

到此這篇關於SQL Server觸發器的文章就介紹到這瞭。希望對大傢的學習有所幫助，也希望大傢多多支持WalkonNet。

推薦閱讀：