觸發器是一種特殊的存儲過程，觸發器主要是通過事件進行觸發而被自動調用執行，而存儲過程必須通過存儲過程的名稱被調用。
一、觸發器的定義
觸發器是在對表進行插入、更新或刪除操作時自動執行的特殊存儲過程。觸發器通常用于強制業務規則，觸發器是一種高級約束，可以定義比CHECK約束更為復雜的約束：可以執行復雜的SQL語句（if/while/case）,可以引用其他表中的列。觸發器定義在特定的表上，與表相關，自動觸發執行，不能直接調用，是一個事務（可回滾）。
二、觸發器分類
SQL SERVER中觸發器可以分為兩類：DML觸發器和DDL觸發器，DML觸發器針對表，DDL觸發器會影響多種數據定義語言語句而觸發，這些語句有create、alter、drop語句。
DML觸發器分為：
1、after觸發器（之后觸發）
- a、insert觸發器
- b、update觸發器
- c、delete觸發器
2、instead of觸發器（之前觸發）
after觸發器要求只有執行某一操作（insert、update、delete）之后觸發器才能被觸發，且只能定義在表上。而instead of觸發器表示并不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身，其優先級高于觸發語句的執行。
觸發器有兩個特殊的表：插入表（instered表）和刪除表(deleted表)
這兩張表是邏輯表也是虛表，觸發器觸發時系統自動在內存中創建這兩張表，不會存儲在數據庫中。這兩張表都是只讀的，不允許修改。這兩張表的結果總是與被觸發器應用的表的結構相同。當觸發器完成工作后，這兩張表就會被刪除。inserted表臨時保存了插入或更新后的記錄行，可以從inserted表中檢查插入的數據是否滿足業務需求，如果不滿足，則向用戶報告錯誤消息，并回滾插入操作。deleted表臨時保存了刪除或更新前的記錄行，可以從deleted表中檢查被刪除的數據是否滿足業務需求，如果不滿足，則向用戶報告錯誤消息，并回滾刪除操作。update數據的時候是先刪除表記錄，然后插入一條記錄，在inserted和deleted表就都有update后的數據記錄了。
inserted表和deleted表存放的信息：
|
修改操作 |
inserted表 |
deleted表 |
|
增加(INSERT)記錄 |
存放新增的記錄 |
無 |
|
刪除(DELETE)記錄 |
無 |
存放被刪除的記錄 |
|
修改(UPDATE)記錄 |
存放更新后的記錄 |
存放更新前的記錄 |
三、創建觸發器
語法：
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL語句
GO
WITH ENCRYPTION表示加密觸發器定義的SQL文本
DELETE, INSERT, UPDATE指定觸發器的類型
1、創建insert類型的觸發器
插入觸發器
--GradeInfo表中插入一條數據，MyStudentInfo表中插入一條記錄
IF (object_id("tr_insert","tr") is not null)
drop trigger tr_insert
GO
CREATE trigger tr_insert
on GradeInfo
after insert --插入觸發
as
begin
--定義變量
declare @GradeId int
--在inserted表中查詢已經插入記錄信息
select @GradeId=id from INSERTED
--MyStudentInfo表中插入數據
insert INTO MyStudentInfo (GradeId) VALUES (@GradeId)
print "插入成功!"
end
插入數據
insert INTO GradeInfo VALUES (11,"C++")
查詢數據
select * from MyStudentInfo where GradeId=11
2、delete觸發器
刪除MyStudentInfo表中的數據，插入備份表
--刪除MyStudentInfo表中的數據，插入備份表
IF (object_id("tr_Delete","tr") is not null)
drop TRIGGER tr_Delete
GO
CREATE trigger tr_Delete
on MyStudentInfo
for delete
as
begin
print "正在備份數據......"
IF (object_id("MyStudentInfo_Back","U") is not null)
--存在表，直接插入數據
insert INTO MyStudentInfo_Back SELECT * from DELETED
else
select * into MyStudentInfo_Back from DELETED
PRINT "備份完成"
end
刪除前查詢MyStudentInfo表數據
select * from MyStudentInfo
刪除id=9的數據
delete FROM MyStudentInfo where Id=9
查詢備份表數據
select * from MyStudentInfo_Back
3、update觸發器
IF (object_id("tr_Update","tr") is not null)
drop TRIGGER tr_Update
GO
CREATE trigger tr_Update
on MyStudentInfo
for update
as
begin
--聲明變量，存儲更新前和更新后的姓名
declare @OldName varchar(16),@NewName varchar(16)
select @OldName=name from DELETED
print "更新前姓名:"+@OldName
select @NewName=name from INSERTED
print "更新后姓名:"+@NewName
end
把張三更新為"張三測試"
update MyStudentInfo SET Name="張三測試" where Id=1
update更新列級觸發器
--update更新列級觸發器
IF (object_id("tr_update_column","tr") is not null)
drop TRIGGER tr_update_column
GO
CREATE trigger tr_update_column
on GradeInfo
for update
as
begin
IF(update(id))
begin
print "系統提示：主鍵ID不能更新"
rollback
end
end
更新id列
update GradeInfo SET Id=15 where Id=4
4、instead of觸發器
instead of觸發器表示并不執行其定義的操作（insert、update、delete）而僅是執行觸發器本身的內容，其優先級高于定義的SQL語句的執行
語法：
create trigger tgr_name
on table_name
with encryption
instead of update...
as
begin
T-SQL
end
創建instead of觸發器
--創建instead of觸發器
/*MyStudentInfo表里面插入數據之前，先判斷GradeInfo表中是否有對應的班級ID，如果沒有，不允許插入，如果存在，則插入 */
IF (object_id("tr_insteadOf","tr") is not null)
drop TRIGGER tr_insteadOf
GO
CREATE trigger tr_insteadOf
on MyStudentInfo
instead of insert
as
begin
IF exists(SELECT * FROM GradeInfo WHERE Id=(SELECT GradeId FROM INSERTED))
print "該班級存在，可以插入"
else
begin
print "該班級不存在，不可以插入"
rollback
end
end
測試1，插入不存在的班級id
insert INTO MyStudentInfo (GradeId) VALUES (15)
測試2，插入存在的班級id
insert INTO MyStudentInfo (GradeId) VALUES (5)
DDL觸發器
create trigger tr_DDL on database for DROP_TABLE,ALTER_TABLE as begin print "別想著刪庫！好好打你的代碼" rollback --回滾 end
測試刪除表
drop TABLE MyStudentInfo
測試修改表結構
alter table MyStudentInfo alter column Name varchar(32)
禁用DML觸發器
disable trigger tr_insteadOf on MyStudentInfo
啟用DML觸發器
enable trigger tr_insteadOf on MyStudentInfo
禁用DDL觸發器
disable trigger tr_DDL on database
啟用DDL觸發器
enable trigger tr_DDL on database
到此這篇關于SQL SERVER觸發器詳解的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文地址：https://www.cnblogs.com/dotnet261010/p/5924238.html
