SQL Server的觸發器詳解

一、概念

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

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

觸發器可以查詢其他表,而且可以包含復雜的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 [email protected]

六、觸發器相關操作

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。

推薦閱讀: