觸發(fā)器的定義

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

觸發(fā)器的作用

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

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

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

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

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

觸發(fā)器的分類

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

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

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

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

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

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

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

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

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

登錄觸發(fā)器

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

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

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

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

INSERTED表：

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

DELETED表：

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

INSERTED表和DELETED表對(duì)照：

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

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

CREATE TRIGGER trigger_name ON table_name

[WITH ENCRYPTION]

FOR | AFTER | INSTEAD

OF [DELETE, INSERT, UPDATE]

AS

T-SQL語(yǔ)句

GO

注：

*WITH ENCRYPTION 表示加密觸發(fā)器定義的SQL文本

DELETE, INSERT, UPDATE指定觸發(fā)器的類型*

觸發(fā)器示例

創(chuàng)建學(xué)生表

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

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

創(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)建存儲(chǔ)學(xué)生人數(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; 
  --把更新后總的學(xué)生數(shù)插入到student_sum表中
end


--測(cè)試觸發(fā)器trig_insert
--功能是向student插入數(shù)據(jù)的同時(shí)級(jí)聯(lián)插入到student_sum表中，更新stuCount
--因?yàn)槭呛笥|發(fā)器，所以先插入數(shù)據(jù)后，才觸發(fā)觸發(fā)器trig_insert;
insert into student(stu_name,stu_gender,stu_age)
values('呂布','男',30);
select stuCount 學(xué)生總?cè)藬?shù) from student_sum;     
insert into student(stu_name,stu_gender,stu_age)
values('貂蟬','女',30);             
select stuCount 學(xué)生總?cè)藬?shù) from student_sum;
insert into student(stu_name,stu_gender,stu_age)
values('曹阿瞞','男',40);                 
select stuCount 學(xué)生總?cè)藬?shù) from student_sum;

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

既然定義了學(xué)生總數(shù)表student_sum表是向student表中插入數(shù)據(jù)后才計(jì)算學(xué)生總數(shù)的，所以學(xué)生總數(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 是用于拋出一個(gè)錯(cuò)誤
  rollback transaction
end
--觸發(fā)觸發(fā)器insert_forbidden
insert student_sum (stuCount) 
values(5);

結(jié)果如下：

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

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

--創(chuàng)建delete觸發(fā)器
create trigger trig_delete
on student after delete
as
begin    
  select stu_id as 已刪除的學(xué)生編號(hào),
  stu_name stu_gender,
  stu_age
  from deleted
end;


--執(zhí)行一條delete語(yǔ)句觸發(fā)trig_delete觸發(fā)器
delete from student where stu_id=1;

結(jié)果如下：

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

UPDATE觸發(fā)器是當(dāng)用戶在指定表上執(zhí)行UPDATE語(yǔ)句時(shí)被調(diào)用被調(diào)用，這種類型的觸發(fā)器用來(lái)約束用戶對(duì)數(shù)據(jù)的修改。UPDATE觸發(fā)器可以執(zhí)行兩種操作：更新前的記錄存儲(chǔ)在DELETED表中，更新后的記錄存儲(chǔ)在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 更新前學(xué)生編號(hào),
  stu_name as 更新前學(xué)生姓名 from deleted;

  select stu_id as 更新后學(xué)生編號(hào),
  stu_name as 更新后學(xué)生姓名 from inserted;
end
--創(chuàng)建完成，執(zhí)行一條update語(yǔ)句觸發(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代碼后，先建立臨時(shí)的INSERTED表和DELETED表，然后執(zhí)行代碼中對(duì)數(shù)據(jù)庫(kù)操作，最后才激活觸發(fā)器中的代碼。而對(duì)于替代(INSTEAD OF)觸發(fā)器,SQL Server服務(wù)器在執(zhí)行觸發(fā)INSTEAD OF觸發(fā)器的代碼時(shí)，先建立臨時(shí)的INSERTED表和DELETED表，然后直接觸發(fā)INSTEAD OF觸發(fā)器，而拒絕執(zhí)行用戶輸入的DML操作語(yǔ)句。

--創(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 '插入年齡錯(cuò)誤' as '失敗原因'
end

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

批注

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