一、觸發器概念
1.1、觸發器特征
1.1.1、觸發器是在對表進行增、刪、改時，自動執行的存儲過程。觸發器常用於強制業務規則，它是一種高級約束，通過事件進行觸發而被執行。
1.1.2、觸發器是一個特殊的事務單元，可以引用其他表中的列執行特殊的業務規則或數據邏輯關系。當出現錯誤時，可以執行rollback transaction操作將整個觸發器以及觸發它的T-SQL語句一並回滾（不需顯示聲明begin transaction）。
1.1.3、每個觸發器將用到的兩個臨時表：
deleted 臨時表：用於臨時存放被刪除的記錄行副本(包括delete和update語句所影響的數據行)；
注意：被刪除的記錄行，首先從原始表中刪除，並保存到觸發器表。然後從觸發器表中刪除，再保存到deleted表。
inserted 臨時表：用於臨時存放插入的記錄行副本(包括insert和update語句所影響的數據行)；
deleted表和inserted表的特征：
> 這兩個表的表結構與該觸發器作用的表相同；
> 這兩個表是邏輯表，並且由系統管理；
> 這兩個表是動態駐留在內存中的(不是存儲在數據庫中)，當觸發器工作完成後，它們也被刪除；
> 這兩個表是只讀的，即只能運用select語句查看(用戶不能直接更改)；
1.1.4、所創建的觸發器(insert、delete、update)是在原表數據行已經修改完成後再觸發。所以，觸發器是在約束檢查之後才執行。
1.2、什麼時候用觸發器？
a、實現主外鍵關系所不能保證的復雜參照完整性和數據的一致性。
不過，通過“級聯引用完整性約束”可以更有效地執行這些更改。
b、防止惡意或錯誤的 INSERT、UPDATE 以及 DELETE 操作，並強制執行比 CHECK 約束定義的限制更為復雜的其他限制。
> 與 CHECK 約束不同(check約束只能引用自身表中的列)，DML觸發器可以引用其他表中的列；
> 觸發器可以完成所有約束的功能，但不一定是最佳方案；
> 觸發器能夠使用自定義信息和較為復雜的錯誤處理；
c、DML 觸發器可以評估數據修改前後表的狀態，並根據該差異采取措施。
d、一個表中的同一個修改語句的DML觸發器，允許被多個不同的操作（INSERT、UPDATE 或 DELETE）來響應；
1.3、觸發器的類型：
insert 觸發器；(略)
delete 觸發器；(略)
update 觸發器：在修改表中記錄行或某列數據時觸發執行；
注意：update(列)函數：實現檢測某列是否被修改。
update 更新操作分為兩步：
首先，“刪除”更改前原有數據行：刪除的原有數據行將復制到deleted臨時表中；
然後，“插入”更改後的新數據行：插入新數據行到原始表，同時將新數據行保存到inserted臨時表和觸發器表中；
1.4、創建觸發器的注意點：
1.4.1、create trigger必須是批處理(go)的第一條語句；
1.4.2、一個觸發器語句只能用到一個表或一個視圖中；
on 表名/ 視圖名
1.4.3、一個觸發器語句可以執行多個操作；
for delete,insert,update -- 無先後順序的任意組合
1.4.4、建議DML觸發器不返回任何結果。這是因為對這些返回結果的特殊處理必須寫入每個允許對觸發器表進行修改的應用程序中。
若要防止從 DML 觸發器返回任何結果，請不要在觸發器定義中包含select語句或變量賦值；
如果必須在觸發器中進行變量賦值，則應該在觸發器被觸發之前使用set nocount on語句以避免返回任何結果集；
注意：未來版本的SQL Server 中，將會刪除從觸發器返回結果集的功能。
1.4.5、如果“觸發器表”本身也存在約束，則在執行insert、delete、update觸發器前，首先會檢查“觸發器表”上存在的約束。如果不滿足約束，則不會執行其insert、delete、update觸發器。
1.5、查看當前數據庫中的所有觸發器
select * from sys.triggers
二、觸發器實例
2.1、初始化表
--------------- 初始化環境 ---------------
create database TriggerDatabase
use TriggerDatabase
go
if exists(select * from sysobjects where name='bank')
drop table bank
create table bank -- 賬戶信息表
(
userName varchar(10) not null, --顧客名
cardID varchar(10) not null, --卡號
currentMoney money not null --當前余額
)
if exists(select * from sysobjects where name='transInfo')
drop table transInfo
create table transInfo --交易信息表
(
cardID varchar(10) not null, --卡號
transType char(4) not null, --交易類型(存入/支取)
transMoney money not null, --交易金額
transDate datetime not null --交易日期
)
go
--------------- 添加約束 ---------------
alter table bank
add constraint CK_currentMoney check(currentMoney>=1);
alter table transInfo
add constraint DF_transDate default(getdate()) for transDate;
alter table transInfo
add constraint CK_transType check(transType in('支取','存入'));
--------------- 添加測試數據 ---------------
/* 張三 1000元 */
insert into bank(userName,cardID,currentMoney)
values('張三','1001 0001',1000);
/* 李四 1元 */
insert into bank(userName,cardID,currentMoney)
values('李四','1001 0002',1);
/* 張三 支取 200元 */
insert into transInfo(cardID,transType,transMoney)
values('1001 0001','支取',200);
--------------- 查看結果 ---------------
select * from bank;
select * from transInfo;
go
2.2、觸發器格式
create trigger [ schema_name. ] -- 觸發器所屬架構
trigger_name -- 觸發器名稱
on { table | view } -- 觸發器的表或視圖
[ with encryption ] -- 加密dml觸發器定義(後面詳解)
{ for | after }
/* after：只有在觸發它的SQL語句執行成功後才能激發。
(只能對“表”定義after) */
{ insert,update,delete }
as
/* SQL語句... */
go
--查看當前數據庫中的所有觸發器
select * from sys.triggers2.3、Insert 觸發器
------------------ insert 觸發器 ------------------
use TriggerDatabase
go
if exists(select * from sysobjects
where name='trig_insert_transInfo')
drop trigger trig_insert_transInfo
go
-- create trigger必須是批處理(go)的第一句
create trigger trig_insert_transInfo
on transInfo for insert
as
declare @_transType char(4), --定義變量
@_transMoney money,
@_cardID char(10),
@balance money --所剩余額
-- 從inserted臨時表中獲取記錄值
select @_transType = transType,
@_transMoney = transMoney,
@_cardID = cardID
from inserted
if(@_transType = '支取')
update bank set currentMoney=currentMoney-@_transMoney
where cardID = @_cardID;
else
update bank set currentMoney=currentMoney+@_transMoney
where cardID = @_cardID;
--顯示交易金額
print '交易成功！ 交易金額：'
+ convert(varchar(20),@_transMoney)
--顯示所剩余額
select @balance = currentMoney from bank
where cardId = @_cardID
print '卡號：'+@_cardID
+ ' 余額：'+convert(varchar(20),@balance);
go
------------------ 測試觸發器 ------------------
-- delete from transInfo
set nocount on --不顯示T-SQL影響的記錄行數
insert into transInfo(cardID,transType,transMoney)
values('1001 0001','支取',200);
insert into transInfo(cardID,transType,transMoney)
values('1001 0001','存入',10000);
--查看結果
select * from bank
select * from transInfo
復制代碼
2.4、delete 觸發器
/* 實現: 當清除'交易信息表'的數據時，
自動備份被清除的數據到backupTable表中
*/
------------------ delete 觸發器 ------------------
use TriggerDatabase
go
if exists (select * from sysobjects
where name='trig_delete_transInfo')
drop trigger trig_delete_transInfo
go
create trigger trig_delete_transInfo
on transInfo after delete -- for | after
as
print '開始備份數據，請稍後......'
-- 如果數據庫中，不存在 backupTable 表
if not exists(select * from sysobjects
where name='backupTable')
select * into backupTable from deleted --deleted臨時表
else
insert into backupTable select * from deleted
print '備份成功，備份表 backupTable 中的數據為：'
select * from backupTable;
go
------------------ 測試觸發器 ------------------
set nocount on
delete from transInfo; --測試
--查看結果
select * from transInfo
select * from backupTable2.5、update 觸發器
------------------ update 觸發器 ------------------
use TriggerDatabase
go
if exists (select * from sysobjects
where name='trig_update_bank')
drop trigger trig_update_bank
go
create trigger trig_update_bank
on bank for update --在bank表上創建update觸發器
as
declare @beforeMoney money,
@afterMoney money,
@currentTrans money --當前交易金額
--從deleted臨時表，獲取交易前的余額
select @beforeMoney = currentMoney from deleted;
--從inserted臨時表，獲取交易後的余額
select @afterMoney = currentMoney from inserted;
if abs(@afterMoney-@beforeMoney) > 2000
begin
print '當前交易金額為：' +
convert(varchar(20),abs(@afterMoney-@beforeMoney))
-- 自定義錯誤消息
raiserror('每次交易金額不能超過2000元，交易失敗！',16,1)
rollback transaction --回滾事務，撤銷交易！
/* 注意：
觸發器是一個特殊的事務單元
不需顯示聲明begin transaction
*/
end
go
------------------ 測試觸發器 ------------------
set nocount on
--測試1: 在 bank表觸發 update觸發器
update bank set currentMoney = currentMoney + 25000
where cardID = '1001 0001'
--測試2: 通過 transInfo表的 trig_insert_transInfo觸發器
-- 間接觸發 bank表的 trig_update_bank觸發器
insert into transInfo(cardID,transType,transMoney)
values('1001 0001','存入',10000);
--查看結果
select * from bank
select * from transInfo
2.6、MSDN參考
2.6.1、加密 dml觸發器定義
若要確保其他用戶不能查看觸發器定義，可以使用with encryption子句加密 dml 觸發器。
使用with encryption子句後，觸發器定義即以無法讀取的格式進行存儲。
觸發器定義加密後，無法進行解密。且任何人都無法進行查看，包括觸發器的所有者和系統管理員。
2.6.2、update() 函數：
可用於確定 insert或 update語句是否影響表中的特定列。
無論何時為列賦值，該函數都將返回 true。
使用if update() 子句示例：
create table testTable(a int null, b int null)
go
create trigger my_trig
on testTable for insert
as
if update(b)
print '列b已被修改！'
go
insert into testTable(b) values(123);
-- drop table testTable
注意：
由於 delete 語句無法只對某列進行刪除，
因此不能將if update()子句應用於delete 語句。
2.6.3、columns_updated() 函數：
也可用於檢查 insert或 update語句更新了表中的哪些列。
此函數使用整數位掩碼指定要測試的列。
使用columns_updated() 函數示例：
create table testTable2(a int null, b int null)
go
create trigger my_trig2
on testTable2 for insert
as
if ( columns_updated() & 2 = 2 )
print '列b已被修改！'
go
insert into testTable2(b) values(123);
-- drop table testTable2
