一、概念

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

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

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

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

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

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

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

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

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

三、語(yǔ)法

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ù)操作語(yǔ)言 (DML) 觸發(fā)器和數(shù)據(jù)定義語(yǔ)言 (DDL) 觸發(fā)器。

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

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

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

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

DML 觸發(fā)器在 INSERT、UPDATE 和 DELETE 語(yǔ)句上操作，并且有助于在表或視圖中修改數(shù)據(jù)時(shí)強(qiáng)制業(yè)務(wù)規(guī)則，擴(kuò)展數(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ù)定義語(yǔ)言 (DDL) 觸發(fā)器

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

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

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

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

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

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

inserted與deleted表

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

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

1、insert觸發(fā)器

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

場(chǎng)景：增加學(xué)生信息時(shí)，要校驗(yàn)其年齡，暫定其年齡必須大于18，否則新增失敗

作用：校驗(yàn)約束

--觸發(fā)器新增：只允許錄取18歲以上學(xué)生
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('學(xué)生年齡必須要大于18哦',16,8)
        rollback tran
    end

2、update觸發(fā)器

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

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

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

場(chǎng)景：專業(yè)信息ID修改，對(duì)應(yīng)的學(xué)生信息中專業(yè)ID也相應(yīng)進(jìn)行修改

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

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

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

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

場(chǎng)景：學(xué)校某選修課取消。

處理邏輯：在刪除課程的同時(shí)，需要?jiǎng)h除該課程的選課信息。

--刪除觸發(fā)器：刪除課程時(shí),同時(shí)刪除該課程的選課信息
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ā)器實(shí)現(xiàn)與實(shí)例3相同的功能，具體實(shí)現(xiàn)代碼如下：

--Instead Of觸發(fā)器：刪除課程時(shí),同時(shí)刪除該課程的選課信息
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
    --獲取要?jiǎng)h除的課程ID
    SELECT @courseId=ID FROM deleted
    --刪除選課信息
    DELETE FROM SC WHERE CourseID = @courseId
    --刪除課程信息
    DELETE FROM Courses WHERE ID=@courseId

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

1、刪除觸發(fā)器

drop trigger 觸發(fā)器名稱

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

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

用查詢分析器重命名或

exec sp_rename 原名稱, 新名稱

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

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

select * from sysobjects where xtype='TR'

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

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

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

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

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

exec sp_helptrigger tbl

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

1、遞歸觸發(fā)器

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

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

2、嵌套觸發(fā)器

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

3、設(shè)置直接遞歸

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

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

也可以EM：數(shù)據(jù)庫(kù)上點(diǎn)右鍵->屬性->選項(xiàng)。

4、設(shè)置間接遞歸、嵌套

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

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

也可以EM：注冊(cè)上點(diǎn)右鍵->屬性->服務(wù)器設(shè)置。

八、觸發(fā)器回滾

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

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

關(guān)鍵在最后兩句，其解釋為：如果更新了 userName 列，就回滾事務(wù)。

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

禁用：

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

啟用：

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

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

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

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

最新評(píng)論