一、概念

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

創(chuàng)建觸發(fā)器時會對其進行定義，以便在對特定表或列作特定類型的數(shù)據(jù)修改時執(zhí)行。

觸發(fā)器可以查詢其他表，而且可以包含復雜的SQL語句。 它們主要用于強制服從復雜的業(yè)務規(guī)則或要求。 例如，您可以根據(jù)客戶當前的帳戶狀態(tài)，控制是否允許插入新訂單。

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

二、使用觸發(fā)器優(yōu)缺點

• 觸發(fā)器可通過數(shù)據(jù)庫中的相關表實現(xiàn)級聯(lián)更改；通過級聯(lián)引用完整性約束可以更有效地執(zhí)行這些更改。
• 觸發(fā)器可以強制比用 CHECK 約束定義的約束更為復雜的約束。與 CHECK 約束不同，觸發(fā)器可以引用其它表中的列。例如，觸發(fā)器可以使用另一個表中的 SELECT 比較插入或更新的數(shù)據(jù)，以及執(zhí)行其它操作，如修改數(shù)據(jù)或顯示用戶定義錯誤信息。
• 觸發(fā)器還可以強制執(zhí)行業(yè)務規(guī)則
• 觸發(fā)器也可以評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。

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

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

使用觸發(fā)器需慎重。

三、語法

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

四、觸發(fā)器類型

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

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

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

1、數(shù)據(jù)操作語言 (DML) 觸發(fā)器

通常說的觸發(fā)器就是DML觸發(fā)器。

DML 觸發(fā)器在 INSERT、UPDATE 和 DELETE 語句上操作，并且有助于在表或視圖中修改數(shù)據(jù)時強制業(yè)務規(guī)則，擴展數(shù)據(jù)完整性。

DML觸發(fā)器又分以下分類：

1、After觸發(fā)器

After觸發(fā)器要求只有執(zhí)行某一操作insert、update、delete之后觸發(fā)器才被觸發(fā)，且只能定義在表上。

• insert觸發(fā)器
• update觸發(fā)器
• delete觸發(fā)器

2、Instead of 觸發(fā)器

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

2、數(shù)據(jù)定義語言 (DDL) 觸發(fā)器

在SQL Server2005后又增加了DDL觸發(fā)器。

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

DDL 觸發(fā)器使用場合：

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

五、DML觸發(fā)器具體應用

在觸發(fā)器實際應用中，主要還是建立約束以及級聯(lián)更新。

inserted與deleted表

觸發(fā)器有兩個特殊的表：插入表（instered表）和刪除表（deleted表）。這兩張是邏輯表也是虛表。有系統(tǒng)在內存中創(chuàng)建者兩張表，不會存儲在數(shù)據(jù)庫中。而且兩張表的都是只讀的，只能讀取數(shù)據(jù)而不能修改數(shù)據(jù)。

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

1、insert觸發(fā)器

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

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

作用：校驗約束

--觸發(fā)器新增：只允許錄取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觸發(fā)器

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

觸發(fā)器檢查deleted表和inserted表以及被更新的表，來確定是否更新了多行以及如何執(zhí)行觸發(fā)器動作。

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

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

--更新觸發(fā)器：更新專業(yè)ID時,同時更新學生的專業(yè)信息
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觸發(fā)器

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

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

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

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

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

--刪除觸發(fā)器：刪除課程時,同時刪除該課程的選課信息
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 觸發(fā)器

用Instead Of觸發(fā)器實現(xiàn)與實例3相同的功能，具體實現(xiàn)代碼如下：

--Instead Of觸發(fā)器：刪除課程時,同時刪除該課程的選課信息
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

六、觸發(fā)器相關操作

1、刪除觸發(fā)器

drop trigger 觸發(fā)器名稱

刪除多個觸發(fā)器：drop trigger 觸發(fā)器名稱,觸發(fā)器名稱

2、重命名觸發(fā)器

用查詢分析器重命名或

exec sp_rename 原名稱, 新名稱

sp_rename 是 SQL Server自帶的一個存儲過程，用于更改當前數(shù)據(jù)庫中用戶創(chuàng)建的對象的名稱，如表名、列表、索引名等。

3、查看數(shù)據(jù)庫中所有的觸發(fā)器

select * from sysobjects where xtype='TR'

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

4、sphelptext 查看觸發(fā)器內容

exec sp_helptext '觸發(fā)器名稱'

5、sp_helptrigger 用于查看觸發(fā)器的屬性

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

exec sp_helptrigger tbl

七、遞歸、嵌套觸發(fā)器

1、遞歸觸發(fā)器

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

• 間接遞歸：對 T1 操作從而觸發(fā) G1，G1 對 T2 操作從而觸發(fā) G2，G2 對 T1 操作從而再次觸發(fā) G1...
• 直接遞歸：對 T1 操作從而觸發(fā) G1，G1 對 T1 操作從而再次觸發(fā) G1...

2、嵌套觸發(fā)器

類似于間接遞歸，間接遞歸必然要形成一個環(huán)，而嵌套觸發(fā)器不一定要形成一個環(huán)，它可以 T1->T2->T3...這樣一直觸發(fā)下去，最多允許嵌套 32 層。

3、設置直接遞歸

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

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

也可以EM：數(shù)據(jù)庫上點右鍵->屬性->選項。

4、設置間接遞歸、嵌套

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

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

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

八、觸發(fā)器回滾

我們看到許多注冊系統(tǒng)在注冊后都不能更改用戶名，但這多半是由應用程序決定的， 如果直接打開數(shù)據(jù)庫表進行更改，同樣可以更改其用戶名，在觸發(fā)器中利用回滾就可以巧妙地實現(xiàn)無法更改用戶名。

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

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

九、禁用、啟用觸發(fā)器

禁用：

alter table 表名 disable trigger 觸發(fā)器名稱

啟用：

alter table 表名 enable trigger 觸發(fā)器名稱

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

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

到此這篇關于SQL Server觸發(fā)器的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

最新評論