概述

觸發器（trigger）是SQL server 提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由事件來觸發，當對一個表進行操作（ insert，delete， update）時就會激活它執行。觸發器經常用於加強數據的完整性約束和業務規則等。

一、觸發器的優點

1.觸發器是自動的。當對錶中的數據做了任何修改之後立即被激活。

2.觸發器可以通過數據庫中的相關表進行層疊修改。

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

二、觸發器的分類

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

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

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

1. insert觸發器:向表中插入數據時被觸發；
2. delete觸發器：從表中刪除數據時被觸發；
3. update觸發器：修改表中數據時被觸發。

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

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

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

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

3.登錄觸發器

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

三、觸發器的工作原理

[DELETE, INSERT, UPDATE]對應為當執行刪除、插入、更新三種動作時被觸發

執行insert時，獲取插入後的數據，可用select*from inserted讀取

執行delete時，獲取刪除前的數據，可用select*from deleted讀取

執行update時，獲取更新前的數據，可用select*from deleted讀取，獲取更新後數據，可用select*from inserted讀取

四、創建觸發器

1、創建觸發器的語法：

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指定觸發器的類型 

2、創建insert觸發器

--創建insert觸發器
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));--創建存儲學生人數的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; --把更新後總的學生數插入到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;

3、創建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;

4、創建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; 

五、管理觸發器

1.查看觸發器

(1).查看數據庫中所有的觸發器

--查看數據庫中所有的觸發器
use 數據庫名
go
select * from sysobjects where xtype='TR'

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

(2).sp_helptext 查看觸發器內容

use 數據庫名
go
exec sp_helptext '觸發器名稱'

將會以表的樣式顯示觸發器內容。

除了觸發器外，sp_helptext 還可以顯示 規則、默認值、未加密的存儲過程、用戶定義函數、視圖的文本。

(3).sp_helptrigger 用於查看觸發器的屬性

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

use 數據庫名
go
exec sp_helptrigger tableName

2.禁用啟用觸發器

禁用:alter table 表名 disable trigger 觸發器名稱

啟用:alter table 表名 enable trigger 觸發器名稱

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

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

3.修改觸發器

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

4.刪除觸發器

 --語法格式:
 DROP TRIGGER { trigger } [ ,...n ]
參數:
 trigger: 要刪除的觸發器名稱 

 n:表示可以刪除多個觸發器的佔位符 

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~