[[284303]]

概述

觸發(fā)器(trigger)是SQL server 提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲過程，它的執(zhí)行不是由程序調(diào)用，也不是手工啟動，而是由事件來觸發(fā)，當(dāng)對一個表進行操作( insert，delete， update)時就會激活它執(zhí)行。觸發(fā)器經(jīng)常用于加強數(shù)據(jù)的完整性約束和業(yè)務(wù)規(guī)則等。 

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

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

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

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

二、觸發(fā)器的分類

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

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

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

insert觸發(fā)器:向表中插入數(shù)據(jù)時被觸發(fā);

delete觸發(fā)器：從表中刪除數(shù)據(jù)時被觸發(fā);

update觸發(fā)器：修改表中數(shù)據(jù)時被觸發(fā)。

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

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

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

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

3.登錄觸發(fā)器

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

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

[DELETE, INSERT, UPDATE]對應(yīng)為當(dāng)執(zhí)行刪除、插入、更新三種動作時被觸發(fā)

執(zhí)行insert時，獲取插入后的數(shù)據(jù)，可用select*from inserted讀取

執(zhí)行delete時，獲取刪除前的數(shù)據(jù)，可用select*from deleted讀取

執(zhí)行update時，獲取更新前的數(shù)據(jù)，可用select*from deleted讀取，獲取更新后數(shù)據(jù)，可用select*from inserted讀取 

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

1、創(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ā)器的類型 

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

--創(chuàng)建insert觸發(fā)器 
create trigger trig_insert 
on student 
after insert 
as 
begin 
 if object_id(N'student_sum',N'U') is null--判斷student_sum表是否存在 
 create table student_sum(stuCount int default(0));--創(chuàng)建存儲學(xué)生人數(shù)的student_sum表 
 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 
--測試觸發(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 學(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; 

3、創(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 已刪除的學(xué)生編號,stu_name stu_gender,stu_age 
 from deleted 
end; 
--執(zhí)行一一條delete語句觸發(fā)trig_delete觸發(fā)器 
delete from student where stu_id=1; 

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

update觸發(fā)器是當(dāng)用戶在指定表上執(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 更新前學(xué)生編號,stu_name as 更新前學(xué)生姓名 from deleted 
 select stu_id as 更新后學(xué)生編號,stu_name as 更新后學(xué)生姓名 from inserted 
end 
--創(chuàng)建完成，執(zhí)行一條update語句觸發(fā)trig_update觸發(fā)器 
update student set stu_name='張飛' where stu_id=2; 

五、管理觸發(fā)器

1.查看觸發(fā)器

(1).查看數(shù)據(jù)庫中所有的觸發(fā)器

--查看數(shù)據(jù)庫中所有的觸發(fā)器 
use 數(shù)據(jù)庫名 
go 
select * from sysobjects where xtype='TR' 

sysobjects 保存著數(shù)據(jù)庫的對象，其中 xtype 為 TR 的記錄即為觸發(fā)器對象。在 name 一列，我們可以看到觸發(fā)器名稱。

(2).sp_helptext 查看觸發(fā)器內(nèi)容

use 數(shù)據(jù)庫名 
go 
exec sp_helptext '觸發(fā)器名稱' 

將會以表的樣式顯示觸發(fā)器內(nèi)容。

除了觸發(fā)器外，sp_helptext 還可以顯示 規(guī)則、默認(rèn)值、未加密的存儲過程、用戶定義函數(shù)、視圖的文本。

(3).sp_helptrigger 用于查看觸發(fā)器的屬性

sp_helptrigger 有兩個參數(shù)：第一個參數(shù)為表名;第二個為觸發(fā)器類型，為 char(6) 類型，可以是 INSERT、UPDATE、DELETE，如果省略則顯示指定表中所有類型觸發(fā)器的屬性。

--查看數(shù)據(jù)庫中所有的觸發(fā)器 
use 數(shù)據(jù)庫名 
go 
select * from sysobjects where xtype='TR' 

use 數(shù)據(jù)庫名goexec sp_helptrigger tableName

2.禁用啟用觸發(fā)器

禁用:alter table 表名 disable trigger 觸發(fā)器名稱

啟用:alter table 表名 enable trigger 觸發(fā)器名稱

如果有多個觸發(fā)器，則各個觸發(fā)器名稱之間用英文逗號隔開。

如果把“觸發(fā)器名稱”換成“ALL”，則表示禁用或啟用該表的全部觸發(fā)器。

3.修改觸發(fā)器

--修改觸發(fā)器語法 
ALTER TRIGGER trigger_name  
 ON table_name  
 [ WITH ENCRYPTION ]  
 FOR {[DELETE][,][INSERT][,][UPDATE]} 
 AS 
 sql_statement; 

4.刪除觸發(fā)器

 --語法格式: 
 DROP TRIGGER { trigger } [ ,...n ] 
參數(shù): 
 trigger: 要刪除的觸發(fā)器名稱 
 n:表示可以刪除多個觸發(fā)器的占位符