觸發(fā)器的定義

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

觸發(fā)器的作用

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

觸發(fā)器的主要作用主要有以下接個方面

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

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

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

觸發(fā)器的分類

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

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

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

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

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

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

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

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

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

登錄觸發(fā)器

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

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

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

• 系統(tǒng)自動在內存中創(chuàng)建INSERTED表或DELETED表；
• 只讀，不允許修改，觸發(fā)器執(zhí)行完成后，自動刪除。

INSERTED表：

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

DELETED表：

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

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)建存儲學生人數的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


--測試觸發(fā)器trig_insert
--功能是向student插入數據的同時級聯(lián)插入到student_sum表中，更新stuCount
--因為是后觸發(fā)器，所以先插入數據后，才觸發(fā)觸發(fā)器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;

執(zhí)行上面的語句后，結果如下圖所示：

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

--創(chuàng)建insert_forbidden,禁止用戶向student_sum表中插入數據
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);

結果如下：

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

用戶執(zhí)行DELETE操作，就會激活DELETE觸發(fā)器，從而控制用戶能夠從數據庫中刪除數據記錄，觸發(fā)DELETE觸發(fā)器后，用戶刪除的記錄會被添加到DELETED表中，原來表的相應記錄被刪除，所以在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;

結果如下：

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

UPDATE觸發(fā)器是當用戶在指定表上執(zhí)行UPDATE語句時被調用被調用，這種類型的觸發(fā)器用來約束用戶對數據的修改。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;

結果如下：

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

與前面介紹的三種AFTER觸發(fā)器不同，SQL Server服務器在執(zhí)行AFTER觸發(fā)器的SQL代碼后，先建立臨時的INSERTED表和DELETED表，然后執(zhí)行代碼中對數據庫操作，最后才激活觸發(fā)器中的代碼。而對于替代(INSTEAD OF)觸發(fā)器,SQL Server服務器在執(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ā)器在早期的數據處理過程中經常使用到，特別是在處理一些因某些動作而需要對其他表進行調整的邏輯時。但是隨著數據量的增長，觸發(fā)器對數據庫的性能影響越來越大，容易造成數據庫性能降低。所以觸發(fā)器在數據量大的場景是禁止使用的，但是其邏輯處理功能還是被一直保留，說明其還是有較深的應用場景，需要我們掌握它的相關用法。