SQL觸發(fā)器是SQL數(shù)據(jù)庫中非常重要，其可以在特定條件下自動執(zhí)行特定的操作，為數(shù)據(jù)庫管理和應(yīng)用開發(fā)帶來很多便利。本文將介紹SQL觸發(fā)器的定義、作用以及使用指南。

觸發(fā)器的定義

觸發(fā)器（trigger）是SQL Server提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，比如當對一個表進行操作（ INSERT，DELETE， UPDATE）時就會激活它執(zhí)行。

觸發(fā)器的作用

觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復雜參照完整性和數(shù)據(jù)的一致性，它能夠?qū)?shù)據(jù)庫中的相關(guān)表進行級聯(lián)修改，提高比CHECK約束更復雜的的數(shù)據(jù)完整性，并自定義錯誤消息。觸發(fā)器作用主要體現(xiàn)在以下幾個方面：

• 強制數(shù)據(jù)庫間的引用完整性
• 級聯(lián)修改數(shù)據(jù)庫中所有相關(guān)的表，自動觸發(fā)其它與之相關(guān)的操作
• 跟蹤變化，撤銷或回滾違法操作，防止非法修改數(shù)據(jù)
• 返回自定義的錯誤消息，約束無法返回信息，而觸發(fā)器可以
• 觸發(fā)器可以調(diào)用更多的存儲過程

觸發(fā)器的優(yōu)點

觸發(fā)器是自動的。當對表中的數(shù)據(jù)做了任何修改之后立即被激活。

觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進行層疊修改。

觸發(fā)器可以強制限制。這些限制比用CHECK約束所定義的更復雜。與CHECK約束不同的是，觸發(fā)器可以引用其他表中的列。

觸發(fā)器的分類

SQL Server包括三種常規(guī)類型的觸發(fā)器：DML觸發(fā)器、DDL觸發(fā)器和登錄觸發(fā)器。

DML(數(shù)據(jù)操作語言,Data Manipulation Language)觸發(fā)器

DML觸發(fā)器是一些附加在特定表或視圖上的操作代碼，當數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)操作語言事件時執(zhí)行這些操作。

SQL Server中的DML觸發(fā)器有三種：

• INSERT觸發(fā)器:向表中插入數(shù)據(jù)時被觸發(fā)；
• DELETE觸發(fā)器：從表中刪除數(shù)據(jù)時被觸發(fā)；
• UPDATE觸發(fā)器：修改表中數(shù)據(jù)時被觸發(fā)。

當遇到下列情形時，應(yīng)考慮使用DML觸發(fā)器：

• 通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改
• 防止惡意或者錯誤的INSERT、DELETE和UPDATE操作，并強制執(zhí)行CHECK約束定義的限制更為復雜的其他限制。
• 評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異才去措施。

DDL(數(shù)據(jù)定義語言,Data Definition Language)觸發(fā)器

DDL觸發(fā)器是當服務(wù)器或者數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言(主要是CREATE，DROP，ALTER開頭的語句)事件時被激活使用，使用DDL觸發(fā)器可以防止對數(shù)據(jù)架構(gòu)進行的某些更改或記錄數(shù)據(jù)中的更改或事件操作。

登錄觸發(fā)器

登錄觸發(fā)器將為響應(yīng) LOGIN 事件而激發(fā)存儲過程。與 SQL Server 實例建立用戶會話時將引發(fā)此事件。登錄觸發(fā)器將在登錄的身份驗證階段完成之后且用戶會話實際建立之前激發(fā)。因此，來自觸發(fā)器內(nèi)部且通常將到達用戶的所有消息（例如錯誤消息和來自 PRINT 語句的消息）會傳送到 SQL Server 錯誤日志。如果身份驗證失敗，將不激發(fā)登錄觸發(fā)器。

觸發(fā)器的工作原理

觸發(fā)器觸發(fā)時：

系統(tǒng)自動在內(nèi)存中創(chuàng)建INSERTED表或DELETED表；

只讀，不允許修改，觸發(fā)器執(zhí)行完成后，自動刪除。

INSERTED表：

臨時保存了插入或更新后的記錄行；

可以從INSERTED表中檢查插入的數(shù)據(jù)是否滿足業(yè)務(wù)需求；

如果不滿足，則向用戶發(fā)送報告錯誤消息，并回滾插入操作?！?/span>

DELETED表：

臨時保存了刪除或更新前的記錄行；

可以從DELETED表中檢查被刪除的數(shù)據(jù)是否滿足業(yè)務(wù)需求；

如果不滿足，則向用戶報告錯誤消息，并回滾插入操作。

INSERTED表和DELETED表對照：　

創(chuàng)建觸發(fā)器

創(chuàng)建觸發(fā)器的語法：

CREATE TRIGGER trigger_name ON table_name 

[WITH ENCRYPTION] 

FOR | AFTER | INSTEAD 

OF [DELETE, INSERT, UPDATE] 

AS 

T-SQL語句

GO

注：

WITH ENCRYPTION 表示加密觸發(fā)器定義的SQL文本
DELETE, INSERT, UPDATE指定觸發(fā)器的類型

觸發(fā)器示例

創(chuàng)建學生表

create table student(    

stu_id int identity(1,1) primary key,    

stu_name varchar(10),    

stu_gender char(2),    

stu_age int

)

(提示：可以左右滑動代碼)

創(chuàng)建INSERT觸發(fā)器

--創(chuàng)建INSERT觸發(fā)器

create trigger trig_insert

on student after insert

as

begin    

  --判斷student_sum表是否存在        

  if object_id(N'student_sum',N'U') is null

  --創(chuàng)建存儲學生人數(shù)的student_sum表    

  create table student_sum(

  stuCount int default(0)

  );

  declare @stuNumber int;

  select @stuNumber = count(*)from student;

  --判斷表中是否有記錄

  if not exists (select * from student_sum)        

  insert into student_sum values(0);

  update student_sum set stuCount =@stuNumber;

  --把更新后總的學生數(shù)插入到student_sum表中

end

--測試觸發(fā)器trig_insert

--功能是向student插入數(shù)據(jù)的同時級聯(lián)插入到student_sum表中，更新stuCount

--因為是后觸發(fā)器，所以先插入數(shù)據(jù)后，才觸發(fā)觸發(fā)器trig_insert;

insert into student(stu_name,stu_gender,stu_age)

values('呂布','男',30);

select stuCount 學生總?cè)藬?shù) from student_sum;     

insert into student(stu_name,stu_gender,stu_age)

values('貂蟬','女',30);             

select stuCount 學生總?cè)藬?shù) from student_sum;

insert into student(stu_name,stu_gender,stu_age)

values('曹阿瞞','男',40);                 

select stuCount 學生總?cè)藬?shù) from student_sum;執(zhí)行上面的語句后，結(jié)果如下圖所示：

既然定義了學生總數(shù)表student_sum表是向student表中插入數(shù)據(jù)后才計算學生總數(shù)的，所以學生總數(shù)表應(yīng)該禁止用戶向其中插入數(shù)據(jù)

--創(chuàng)建insert_forbidden,禁止用戶向student_sum表中插入數(shù)據(jù)

create trigger insert_forbidden

on student_sum after insert

as

begin    

  RAISERROR('禁止直接向該表中插入記錄，操作被禁止',1,1)

  --raiserror 是用于拋出一個錯誤

  rollback transaction

end

--觸發(fā)觸發(fā)器insert_forbidden

insert student_sum (stuCount)

values(5);

結(jié)果如下：

創(chuàng)建DELETE觸發(fā)器

用戶執(zhí)行DELETE操作，就會激活DELETE觸發(fā)器，從而控制用戶能夠從數(shù)據(jù)庫中刪除數(shù)據(jù)記錄，觸發(fā)DELETE觸發(fā)器后，用戶刪除的記錄會被添加到DELETED表中，原來表的相應(yīng)記錄被刪除，所以在DELETED表中查看刪除的記錄。

--創(chuàng)建delete觸發(fā)器

create trigger trig_delete

on student after delete

as

begin    

  select stu_id as 已刪除的學生編號,

  stu_name stu_gender,

  stu_age

  from deleted

end;

--執(zhí)行一條delete語句觸發(fā)trig_delete觸發(fā)器

delete from student where stu_id=1;

結(jié)果如下：

創(chuàng)建UPDATE觸發(fā)器

UPDATE觸發(fā)器是當用戶在指定表上執(zhí)行UPDATE語句時被調(diào)用被調(diào)用，這種類型的觸發(fā)器用來約束用戶對數(shù)據(jù)的修改。UPDATE觸發(fā)器可以執(zhí)行兩種操作：更新前的記錄存儲在DELETED表中，更新后的記錄存儲在INSERTED表中。

--創(chuàng)建update觸發(fā)器

create trigger trig_update

on student after update

as

begin    

  declare @stuCount int;

  select @stuCount=count(*) from student;

  update student_sum set stuCount =@stuCount;

  select stu_id as 更新前學生編號,

  stu_name as 更新前學生姓名 from deleted;

  select stu_id as 更新后學生編號,

  stu_name as 更新后學生姓名 from inserted;

end

--創(chuàng)建完成，執(zhí)行一條update語句觸發(fā)trig_update觸發(fā)器

update student set stu_name='張飛'

where stu_id=2;

結(jié)果如下：

創(chuàng)建替代觸發(fā)器

與前面介紹的三種AFTER觸發(fā)器不同，SQL Server服務(wù)器在執(zhí)行AFTER觸發(fā)器的SQL代碼后，先建立臨時的INSERTED表和DELETED表，然后執(zhí)行代碼中對數(shù)據(jù)庫操作，最后才激活觸發(fā)器中的代碼。而對于替代(INSTEAD OF)觸發(fā)器,SQL Server服務(wù)器在執(zhí)行觸發(fā)INSTEAD OF觸發(fā)器的代碼時，先建立臨時的INSERTED表和DELETED表，然后直接觸發(fā)INSTEAD OF觸發(fā)器，而拒絕執(zhí)行用戶輸入的DML操作語句。

--創(chuàng)建instead of觸發(fā)器

create trigger trig_insteadOf

on student instead of insert

as

begin    

  declare @stuAge int;

  select @stuAge=(select stu_age from inserted)

  if(@stuAge >120)

  select '插入年齡錯誤' as '失敗原因'

End

創(chuàng)建完成，執(zhí)行一條INSERT語句觸發(fā)觸發(fā)器trig_insteadOf

批注

觸發(fā)器在早期的數(shù)據(jù)處理過程中經(jīng)常使用到，特別是在處理一些因某些動作而需要對其他表進行調(diào)整的邏輯時。但是隨著數(shù)據(jù)量的增長，觸發(fā)器對數(shù)據(jù)庫的性能影響越來越大，容易造成數(shù)據(jù)庫性能降低。所以觸發(fā)器在數(shù)據(jù)量大的場景是禁止使用的，但是其邏輯處理功能還是被一直保留，說明其還是有較深的應(yīng)用場景，需要我們掌握它的相關(guān)用法。

重要聲明：本文來自SQL數(shù)據(jù)庫開發(fā)，經(jīng)授權(quán)轉(zhuǎn)載，版權(quán)歸原作者所有，不代表銳成觀點，轉(zhuǎn)載的目的在于傳遞更多知識和信息。