SQL Server的觸發器詳解

2022-05-19 19:02:31

一、概念

觸發器是一種特殊型別的儲存過程，不由使用者直接呼叫。

建立觸發器時會對其進行定義，以便在對特定表或列作特定型別的資料修改時執行。

觸發器可以查詢其他表，而且可以包含複雜的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觸發器的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。