觸發器的特征：

　　1、觸發器是在對表進行增、刪、改時，自動執行的存儲過程。觸發器常用于強制業務規則，它是一種高級約束，通過事件進行觸發而被執行。

　　2、觸發器是一個特殊的事務單元，可以引用其他表中的列執行特殊的業務規則或數據邏輯關系。當出現錯誤時，可以執行rollback transaction操作將整個觸發器以及觸發它的T-SQL語句一并回滾（不需顯示聲明begin transaction）。

　　3、每個觸發器將用到的兩個臨時表：

　　deleted 臨時表：用于臨時存放被刪除的記錄行副本(包括delete和update語句所影響的數據行)；

　　注意：被刪除的記錄行，首先從原始表中刪除，并保存到觸發器表。然后從觸發器表中刪除，再保存到deleted表。

　　inserted臨時表：用于臨時存放插入的記錄行副本(包括insert和update語句所影響的數據行)；

　　deleted表和inserted表的特征：

　　> 這兩個表的表結構與該觸發器作用的表相同；

　　> 這兩個表是邏輯表，并且由系統管理；

　　> 這兩個表是動態駐留在內存中的(不是存儲在數據庫中)，當觸發器工作完成后，它們也被刪除；

　　> 這兩個表是只讀的，即只能運用select語句查看(用戶不能直接更改)；

　　4、所創建的觸發器(insert、delete、update)是在原表數據行已經修改完成后再觸發。所以，觸發器是在約束檢查之后才執行。

　　什么時候使用觸發器？

　　a、實現主外鍵關系所不能保證的復雜參照完整性和數據的一致性。

　　不過，通過“級聯引用完整性約束”可以更有效地執行這些更改。

　　b、防止惡意或錯誤的 INSERT、UPDATE 以及 DELETE 操作，并強制執行比 CHECK 約束定義的限制更為復雜的其他限制。

　　> 與 CHECK 約束不同(check約束只能引用自身表中的列)，DML觸發器可以引用其他表中的列；

　　> 觸發器可以完成所有約束的功能，但不一定是最佳方案；

　　> 觸發器能夠使用自定義信息和較為復雜的錯誤處理；

　　c、DML 觸發器可以評估數據修改前后表的狀態，并根據該差異采取措施。

　　d、一個表中的同一個修改語句的DML觸發器，允許被多個不同的操作（INSERT、UPDATE 或 DELETE）來響應；

　　觸發器的類型：

　　insert 觸發器；(略)

　　delete 觸發器；(略)

　　update 觸發器：在修改表中記錄行或某列數據時觸發執行；

　　注意：update(列)函數：實現檢測某列是否被修改。

　　update 更新操作分為兩步：

　　首先，“刪除”更改前原有數據行：刪除的原有數據行將復制到deleted臨時表中；

　　然后，“插入”更改后的新數據行：插入新數據行到原始表，同時將新數據行保存到inserted臨時表和觸發器表中；

　　創建觸發器的注意點：

　　1、create trigger必須是批處理(go)的第一條語句；

　　2、一個觸發器語句只能用到一個表或一個視圖中；

　　on 表名/ 視圖名

　　3、一個觸發器語句可以執行多個操作；

　　for delete,insert,update -- 無先后順序的任意組合

　　4、建議DML觸發器不返回任何結果。這是因為對這些返回結果的特殊處理必須寫入每個允許對觸發器表進行修改的應用程序中。

　　若要防止從 DML 觸發器返回任何結果，請不要在觸發器定義中包含select語句或變量賦值；

　　如果必須在觸發器中進行變量賦值，則應該在觸發器被觸發之前使用set nocount on語句以避免返回任何結果集；

　　注意：未來版本的SQL Server 中，將會刪除從觸發器返回結果集的功能。

　　5、如果“觸發器表”本身也存在約束，則在執行insert、delete、update觸發器前，首先會檢查“觸發器表”上存在的約束。如果不滿足約束，則不會執行其insert、delete、update觸發器。

　　查看當前數據庫中的所有觸發器

　　select * from sys.triggers

　　創建臨時表 #tableName

　　create table #tableName

　　如何使用 SQL Server 觸發器

　　觸發器2_初始化環境SQL

　　初始化環境

---------------　初始化環境　---------------
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

　　觸發器3_定義觸發器的格式

　　定義觸發器的格式

--　=============================================
--　Author:　　　　　　　　xugang
--　Create　date:　2010-2-14
--　Description:　　　　定義觸發器的精簡格式
--　　[　]：可選　　　　　{　}必選
--　=============================================
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.triggers

　　觸發器4_insert 觸發器SQL

　　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

　　觸發器5_delete 觸發器SQL

　　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　backupTable

　　觸發器6_update 觸發器SQL

　　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

　　觸發器7_MSDN參考

　　加密 dml觸發器定義

　　若要確保其他用戶不能查看觸發器定義，可以使用with encryption子句加密 dml 觸發器。

　　使用with encryption子句后，觸發器定義即以無法讀取的格式進行存儲。

　　觸發器定義加密后，無法進行解密。且任何人都無法進行查看，包括觸發器的所有者和系統管理員。

　　update() 函數：

　　可用于確定 insert或 update語句是否影響表中的特定列。

　　無論何時為列賦值，該函數都將返回 true。

　　使用if update() 子句示例：

　　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 語句。

　　columns_updated() 函數：

　　也可用于檢查 insert或 update語句更新了表中的哪些列。

　　此函數使用整數位掩碼指定要測試的列。

　　使用columns_updated() 函數示例：

　　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

關鍵字：SQL Server、觸發器、數據庫


【 頂部 】 【 關閉 】