觸發器的定義

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

觸發器的作用

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一致性，它能夠對資料庫中的相關表進行級聯修改，提高比CHECK約束更複雜的的數據完整性，並自定義錯誤消息。

觸發器的主要作用主要有以下接個方面

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

觸發器的優點

• 觸發器是自動的。當對表中的數據做了任何修改之後立即被激活。
• 觸發器可以通過資料庫中的相關表進行層疊修改。
• 觸發器可以強制限制。這些限制比用CHECK約束所定義的更複雜。與CHECK約束不同的是，觸發器可以引用其他表中的列。

觸發器的分類

SQL Server包括三種常規類型的觸發器：DML觸發器、DDL觸發器和登錄觸發器。

DML(數據操作語言,Data Manipulation Language)觸發器

DML觸發器是一些附加在特定表或視圖上的操作代碼，當資料庫伺服器中發生數據操作語言事件時執行這些操作。

SQL Server中的DML觸發器有三種：

• INSERT觸發器:向表中插入數據時被觸發；
• DELETE觸發器：從表中刪除數據時被觸發；
• UPDATE觸發器：修改表中數據時被觸發。

當遇到下列情形時，應考慮使用DML觸發器：

• 通過資料庫中的相關表實現級聯更改
• 防止惡意或者錯誤的INSERT、DELETE和UPDATE操作，並強制執行CHECK約束定義的限制更為複雜的其他限制。
• 評估數據修改前後表的狀態，並根據該差異才去措施。

DDL(數據定義語言,Data Definition Language)觸發器

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

登錄觸發器

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

觸發器的工作原理

觸發器觸發時：

• 系統自動在內存中創建INSERTED表或DELETED表；
• 只讀，不允許修改，觸發器執行完成後，自動刪除。

INSERTED表：

• 臨時保存了插入或更新後的記錄行；
• 可以從INSERTED表中檢查插入的數據是否滿足業務需求；
• 如果不滿足，則向用戶發送報告錯誤消息，並回滾插入操作。

DELETED表：

• 臨時保存了刪除或更新前的記錄行；
• 可以從DELETED表中檢查被刪除的數據是否滿足業務需求；
• 如果不滿足，則向用戶報告錯誤消息，並回滾插入操作。

INSERTED表和DELETED表對照：

創建觸發器

創建觸發器的語法：

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE] AS T-SQL語句GO

註：

WITH ENCRYPTION 表示加密觸發器定義的SQL文本

DELETE, INSERT, UPDATE指定觸發器的類型

觸發器示例

創建學生表

create table student( 
stu_id int identity(1,1) primary key, 
stu_name varchar(10), 
stu_gender char(2), 
stu_age int
)

創建INSERT觸發器

--創建INSERT觸發器
create trigger trig_insert
on student after insert
as
begin 
 --判斷student_sum表是否存在 
 if object_id(N'student_sum',N'U') is null
 --創建存儲學生人數的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; 
 --把更新後總的學生數插入到student_sum表中
end
--測試觸發器trig_insert
--功能是向student插入數據的同時級聯插入到student_sum表中，更新stuCount
--因為是後觸發器，所以先插入數據後，才觸發觸發器trig_insert;
insert into student(stu_name,stu_gender,stu_age)
values('呂布','男',30);
select stuCount 學生總人數 from student_sum; 
insert into student(stu_name,stu_gender,stu_age)
values('貂蟬','女',30); 
select stuCount 學生總人數 from student_sum;
insert into student(stu_name,stu_gender,stu_age)
values('曹阿瞞','男',40); 
select stuCount 學生總人數 from student_sum;

執行上面的語句後，結果如下圖所示：

既然定義了學生總數表student_sum表是向student表中插入數據後才計算學生總數的，所以學生總數表應該禁止用戶向其中插入數據

--創建insert_forbidden,禁止用戶向student_sum表中插入數據
create trigger insert_forbidden
on student_sum after insert
as
begin 
 RAISERROR('禁止直接向該表中插入記錄，操作被禁止',1,1)
 --raiserror 是用於拋出一個錯誤
 rollback transaction
end
--觸發觸發器insert_forbidden
insert student_sum (stuCount) 
values(5);

結果如下：

創建DELETE觸發器

用戶執行DELETE操作，就會激活DELETE觸發器，從而控制用戶能夠從資料庫中刪除數據記錄，觸發DELETE觸發器後，用戶刪除的記錄會被添加到DELETED表中，原來表的相應記錄被刪除，所以在DELETED表中查看刪除的記錄。

--創建delete觸發器
create trigger trig_delete
on student after delete
as
begin 
 select stu_id as 已刪除的學生編號,
 stu_name stu_gender,
 stu_age
 from deleted
end;
--執行一條delete語句觸發trig_delete觸發器
delete from student where stu_id=1;

結果如下：

創建UPDATE觸發器

UPDATE觸發器是當用戶在指定表上執行UPDATE語句時被調用被調用，這種類型的觸發器用來約束用戶對數據的修改。UPDATE觸發器可以執行兩種操作：更新前的記錄存儲在DELETED表中，更新後的記錄存儲在INSERTED表中。

--創建update觸發器
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
--創建完成，執行一條update語句觸發trig_update觸發器
update student set stu_name='張飛' 
where stu_id=2;

結果如下：

創建替代觸發器

與前面介紹的三種AFTER觸發器不同，SQL Server伺服器在執行AFTER觸發器的SQL代碼後，先建立臨時的INSERTED表和DELETED表，然後執行代碼中對資料庫操作，最後才激活觸發器中的代碼。而對於替代(INSTEAD OF)觸發器,SQL Server伺服器在執行觸發INSTEAD OF觸發器的代碼時，先建立臨時的INSERTED表和DELETED表，然後直接觸發INSTEAD OF觸發器，而拒絕執行用戶輸入的DML操作語句。

--創建instead of觸發器 
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

創建完成，執行一條INSERT語句觸發觸發器trig_insteadOf

批註

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