同事剛在問 觸發程序要如何使用, 他希望能夠在資料庫中就能用TRIGGER做出一些異動, 減輕程式的負擔.
找了一下資料給他.
下班後也來玩玩看^^
TRIGGER觸發程序的說明與介紹
關建字：SQL 觸發程序
--------------------------------------------------------------------------------
[作者： Robin ] [時間：03/05/07 01:08:50 ] [文章類型：轉載 ]
--------------------------------------------------------------------------------
平均推薦度： 尚無人評分 ( 分 )
這是微軟的SQL線上書說明文件
CREATE TRIGGER
建立觸發程序。觸發程序是當使用者在指定的資料上嘗試執行指定的資料變動陳述式時，便會自動執行的特殊預存程序。Microsoft® SQL Server™ 允許針對任意給定的 INSERT、UPDATE、或 DELETE 陳述式建立多重觸發程序。
語法
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ ( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NO FOR REPLICATION ]
AS
{ IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
引數
trigger_name
是觸發程序的名稱。觸發程序名稱必須遵守識別項規則，並且在資料庫中必須是唯一的。指定觸發程序的擁有者名稱是選擇性的。
Table | view
是執行觸發程序所在的資料表或檢視表，有時候稱為觸發程序資料表或觸發程序檢視表。指定資料表或檢視表的擁有者名稱是選擇性的。
WITH ENCRYPTION
將含有 CREATE TRIGGER 文字的syscomments 項目進行加密。使用 WITH ENCRYPTION 防止將觸發程序發行為 SQL Server 複寫的一部份。
AFTER
指定只有當觸發的 SQL 陳述式中指定的所有作業已經成功地執行時，才啟動觸發程序。在此觸發程序執行之前，也必須順利完成所有參考的串聯動作及條件約束檢查。
如果只有指定關鍵字 FOR，AFTER 為預設值。
無法檢視表上定義 AFTER 觸發程序。
INSTEAD OF
指定執行觸發程序以取代觸發的 SQL 陳述式，因而替代觸發陳述式的動作。
在資料表或檢視表上，每一個 INSERT、UPDATE、或 DELETE 陳述式最多可以定義一個 INSTEAD OF 觸發程序。但是，可以在擁有自己的 INSTEAD OF 觸發程序的檢視表上定義檢視表。
使用 WITH CHECK OPTION 的可更新檢視表不允許 INSTEAD OF 觸發程序。如果新增 INSTEAD OF 觸發程序至指定 WITH CHECK OPTION 的可更新檢視表，SQL Server 將會產生錯誤訊息。在定義 INSTEAD OF 觸發程序之前，使用者必須使用 ALTER VIEW 移除該選項。
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
指定資料修改陳述式的關鍵字，在嘗試對此資料表或檢視表使用時，會啟動觸發程序。至少必須指定一個選項。在觸發程序定義中，允許這些選項以任意的順序進行任意的組合。如果指定一個以上的選項，請以逗點分隔選項。
針對 INSTEAD OF 觸發程序，在具有指定 ON DELETE 串聯動作之參考關係的資料表上不允許 DELETE 選項。同樣地，在具有指定 ON UPDATE 串聯動作之參考關係的資料表上不允許 UPDATE 選項。
WITH APPEND
指定應新增現有型別的額外觸發程序。只有當相容性等級為 65 或更低時，才需要使用這個選擇性的子句。如果相容性等級為 70 或更高，不需要使用 WITH APPEND 子句來新增現有型別的額外觸發程序 (當相容性等級設定值為 70 或更高時，此為 CREATE TRIGGER 的預設行為)。如需詳細資訊，請參閱 sp_dbcmptlevel。
WITH APPEND 無法和 INSTEAD OF 觸發程序或 AFTER 觸發程序一起使用。只有當為了回溯相容性而指定 FOR (無 INSTEAD OF 或 AFTER) 時，才可以使用 WITH APPEND。在未來的版本中，將不支援 WITH APPEND 及 FOR (解譯為 AFTER)。
NOT FOR REPLICATION
表示當複寫處理程序修改此觸發程序所包含的資料表時，不執行此觸發程序。
AS
是觸發程序所要執行的動作。
sql_statement
觸發程序的條件及動作。觸發程序條件指定額外的條件，以決定嘗試的 DELETE、INSERT、或 UPDATE 陳述式是否啟動觸發程序動作的執行。
當嘗試 DELETE、INSERT、或 UPDATE 作業時，便會執行以 Transact-SQL 陳述式指定的觸發程序動作。
觸發程序可以包含任意數目與任意種類的 Transact-SQL 陳述式。設計觸發程序以根據資料變動陳述式檢查或變更資料；不需要傳回資料給使用者。觸發程序中的 Transact-SQL 陳述式通常包含流程控制語言。在 CREATE TRIGGER 陳述式中可以使用某些特別資料表：
邏輯 (概念) 資料表：deleted 及 inserted。它們在結構上類似於定義觸發程序的資料表 (亦即，使用者嘗試執行動作的資料表)，並且保留可能被使用者動作變更之資料列的舊值及新值。例如，若要擷取 deleted 資料表中所有的值，請使用：
SELECT *
FROM deleted
在 DELETE、INSERT、或 UPDATE 觸發程序中，如果相容性等級等於 70，SQL Server 不允許在 inserted 及 deleted 資料表中參考 text、ntext、或 image 資料行。無法存取 inserted 及 deleted 資料表中的 text、ntext、及 image 值。若要在 INSERT 或 UPDATE 觸發程序中擷取新值，請聯結 inserted 資料表及原始更新資料表。當相容性等級為 65 或更低時，允許 null 值的 inserted 或 deleted 的 text、ntext、或 image 資料行將傳回 null 值；如果這些資料行不允許 null，則傳回長度為 0 的字串。
如果相容性等級為 80 或更高時，SQL Server 允許透過資料表或檢視表上的 INSTEAD OF 觸發程序更新 text、ntext、或 image 資料行。
n
是表示在此觸發程序中可以包含多重 Transact-SQL 陳述式的替代符號。對於 IF UPDATE (column) 陳述式，藉由重複 UPDATE (column) 子句便可以加入多重資料行。
IF UPDATE (column)
測試針對所指定資料行的 INSERT 或 UPDATE 動作，並且不含使用 DELETE 作業。可以指定一個以上的資料行。由於資料表名稱是在 ON 子句中指定，請勿在 IF UPDATE 子句中的資料行名稱之前加入資料表名稱。若要測試多個資料行的 INSERT 或 UPDATE 動作，請在第一個 UPDATE(column) 子句後面指定各別的 UPDATE(column) 子句。假如 UPDATE 在 INSERT 動作傳回 TRUE 值，因為資料行不是有外顯的值就是有內隱的 (NULL) 值插入。
附註 IF UPDATE (column) 子句和 IF、IF...ELSE 或 WHILE 陳述式的功能相同，並且可以使用 BEGIN...END 區塊。如需詳細資訊，請參閱流程控制語言。
可以在觸發程序本文中的任意位置使用 UPDATE(column)。
column
是要測試 INSERT 或 UPDATE 動作的資料行名稱。此資料行可以是 SQL Server 所支援的任意資料型別。但是，在此處不可以使用計算的資料行。如需詳細資訊，請參閱資料型別。
IF (COLUMNS_UPDATED())
測試監視的資料行是否插入或更新資料。只限定在 INSERT 或 UPDATE 觸發程序中使用。COLUMNS_UPDATED 傳回 varbinary 位元比對模式，以表示資料表中的哪些資料行已插入或更新資料。
COLUMNS_UPDATED 函數所傳回位元的順序是由左至右，最小的 significant 位元為最左邊。COLUMNS_UPDATED 在 INSERT 動作的所有資料行將傳回 TRUE 值，因為資料行不是有外顯的值就是有內隱的 (NULL) 值插入。最左邊的位元代表資料表中第一個資料行，右邊的下一個位元代表第二個資料行，以此類推。如果建立觸發程序的資料表包含 8 個以上的資料行，COLUMNS_UPDATED 將傳回多個位元組，最小的 significant 位元為最左邊。
可以在觸發程序本文中的任意位置使用 COLUMNS_UPDATED。
bitwise_operator
是在比較中使用的位元運算子。
updated_bitmask
是真正已更新或插入資料的資料行的整數位元遮罩。例如，資料表 t1 包含資料行 C1、C2、C3、C4、及 C5。若要檢查資料行 C2、C3、及 C4 是否都已更新 (資料表 t1 具有 UPDATE 觸發程序)，請將值設定為 14。若只要檢查資料行 C2 是否已更新，請將值設定為 2。
comparison_operator
是比較運算子。使用等號 (=) 檢查 updated_bitmask 中指定的所有資料行是否都已更新。使用大於符號 (>) 檢查 updated_bitmask 中指定的任意或某些資料行是否已更新。
column_bitmask
是用來檢查資料行是否已更新或插入資料的整數位元遮罩。
備註
觸發程序通常用來強制執行商業規則及資料完整性。SQL Server 透過資料表建立陳述式 (ALTER TABLE 及 CREATE TABLE) 提供宣告性參考完整性 (DRI)；然而，DRI 不提供跨資料庫的參考完整性。若要強制維持參考完整性 (關於資料表的主索引鍵與外部索引鍵之間關係的規則)，請使用主索引鍵及外部索引鍵條件約束 (ALTER TABLE 及 CREATE TABLE 的 PRIMARY KEY 及 FOREIGN KEY 關鍵字)。 如果觸發資料表上存在條件約束，在 INSTEAD OF 觸發程序執行之後及 AFTER 觸發程序執行之前，將會檢查這些條件約束。 如果違反條件約束，將復原 INSTEAD OF 觸發程序動作，並且不會執行 (引發) AFTER 觸發程序。
可以使用 sp_settriggerorder指定欲在資料表上執行的第一與結尾的 AFTER 觸發程序。一個資料表上只能指定一個第一及一個結尾 AFTER 觸發程序；如果在同一個資料表上有其它 AFTER 觸發程序，則以隨機的順序執行它們。
如果 ALTER TRIGGER 陳述式變更第一或結尾觸發程序，將卸除在已修改的觸發程序上所設定的第一或結尾屬性，並且必須使用 sp_settriggerorder 重新設定順序值。
只有在觸發的 SQL 陳述式 (包括與被更新或被刪除的物件相關聯的所有參考的串聯動作及條件約束檢查) 執行成功之後，才會執行 AFTER 觸發程序。AFTER 觸發程序可以查看觸發陳述式及由觸發陳述式所引起的所有參考串聯 UPDATE 與 DELETE 動作的影響。
觸發程序的限制
CREATE TRIGGER 必須是批次處理中的第一個陳述式，並且只可以套用於一個資料表。
觸發程序只能建立在目前的資料庫中；但是，觸發程序可以參考目前資料庫以外的物件。
如果指定觸發程序的擁有者名稱 (以限定觸發程序)，請以相同的方式限定資料表名稱。
在同一個 CREATE TRIGGER 陳述式中，可以為多個使用者動作 (例如：INSERT 與 UPDATE) 定義相同的觸發程序動作。
在具有串聯已定義 DELETE/UPDATE 動作之外部索引鍵的資料表上，無法定義 INSTEAD OF DELETE/UPDATE 觸發程序。
在觸發程序內可以指定任意的 SET 陳述式。選擇的 SET 選項在觸發程序執行期間仍然維持效果，然後恢復為先前的設定值。
當觸發程序啟動時，結果將傳回給呼叫的應用程式，就像預存程序一樣。若要消除觸動觸發程序而傳回給應用程式的結果，請不要加入傳回結果的 SELECT 陳述式或在觸發程序中執行變數指派的陳述式。觸發程序若包含傳回結果給使用者的 SELECT 陳述式或執行變數指派的陳述式，則需要特別的處理；必須將這些傳回的結果寫入允許變動觸發資料表的每一個應用程式。如果必須在觸發程序指派變數，則可在觸發程序的起始位置使用 SET NOCOUNT 陳述式以防止傳回任何結果。
DELETE 觸發程序無法捕捉 TRUNCATE TABLE 陳述式。雖然事實上，TRUNCATE TABLE 陳述式等於不含 WHERE 子句 (移除所有資料列) 的 DELETE，但是它不會被記錄，也因此無法執行觸發程序。因為 TRUNCATE TABLE 陳述式的使用權限預設為資料表的擁有者並且無法轉移，所以只有資料表的擁有者需要注意粗心使用 TRUNCATE TABLE 陳述式而避開 DELETE 觸發程序的情況。
無論已記錄或未記錄，WRITETEXT 陳述式都不會啟動觸發程序。
觸發程序中不允許下列 Transact-SQL 陳述式：
ALTER DATABASE CREATE DATABASE DISK INIT
DISK RESIZE DROP DATABASE LOAD DATABASE
LOAD LOG RECONFIGURE RESTORE DATABASE
RESTORE LOG
附註 因為 SQL Server 不支援使用者在系統資料表上自訂觸發程序，所以建議您不要在系統資料表上建立使用者自訂的觸發程序。
多重觸發程序
SQL Server 允許為每一個資料變動事件 (DELETE、INSERT、或 UPDATE) 建立多重觸發程序。例如，如果在已經具有一個 UPDATE 觸發程序的資料表上執行 CREATE TRIGGER FOR UPDATE，將會建立新的更新觸發程序。在先前的版本中，針對每一資料表，只允許為每一個資料變動事件 (INSERT、UPDATE、DELETE) 建立一個觸發程序。
附註 如果觸發程序的名稱不同，CREATE TRIGGER (具有相容性等級 70) 的預設行為是新增額外的觸發程序。如果觸發程序的名稱相同，SQL Server 將傳回錯誤訊息。然而，如果相容性等級等於或小於 65，即使觸發程序名稱不同，任何以 CREATE TRIGGER 陳述式建立的新觸發程序將會取代相同類型的現有觸發程序。若需相關資訊，請參閱 sp_dbcmptlevel。
遞迴觸發程序
當在 sp_dboption 中啟用 [遞迴觸發程序] 設定值時，SQL Server 也允許以遞迴的方式呼叫觸發程序。
遞迴觸發程序允許發生兩種類型的遞迴：
間接遞迴
直接遞迴
使用間接遞迴，應用程式更新資料表 T1，觸動觸發程式 TR1 更新資料表 T2。在這個分析藍本中，然後會觸動觸發程序 T2 並更新資料表 T1。
使用直接遞迴，應用程式更新資料表 T1，觸動觸發程式 TR1 更新資料表 T1。因為資料表 T1 被更新，所以再次觸動觸發程序 TR1，以此類推。
本範例將同時使用間接及直接觸發程序遞迴。假設在資料表 T1 上定義更新觸發程序 TR1 及 TR2。觸發程序 TR1 會以遞迴的方式更新資料表 T1。一個 UPDATE 陳述式會執行 TR1 及 TR2 各一次。此外，執行 TR1 會觸動 TR1 (遞迴) 及 TR2 的執行。給定觸發程序的 inserted 及 deleted 資料表只包含對應於引起觸發程序之 UPDATE 陳述式的資料列。
附註 只有在啟用 sp_dboption 的 [遞迴觸發程序] 設定值時，上述的行為才會發生。針對給定事件所定義的多重觸發程序並未定義執行的順序。每一個觸發程序應該是各自獨立的。
停用 [遞迴觸發程序] 設定值只能防止直接遞迴。若要也停用間接遞迴，請使用 sp_configure 將 [巢狀觸發程序] 伺服器選項設定為 0。
如果任何觸發程序執行 ROLLBACK TRANSACTION，則不論巢狀的層級，將不會再執行其它觸發程序。
巢狀觸發程序
觸發程序的巢狀結構最多 32 層。如果觸發程序變更含有其它觸發程序的資料表，則啟動第二個觸發程序，然後可以呼叫第三個觸發程序，以此類推。如果鏈中的任一觸發程序引發無限迴圈，巢狀層級將會超過限制並取消該觸發程序。若要停用巢狀觸發程序，請將 sp_configure 的 [巢狀觸發程序] 選項設定為0 (關閉)。預設的設定值允許巢狀觸發程序。如果停用巢狀觸發程序，也會停用遞迴觸發程序，不論 sp_dboption 的 [遞迴觸發程序] 設定值為何。
延緩名稱解析
SQL Server 允許 Transact-SQL 預存程序、觸發程序、及批次處理參考在編譯時期不存在的資料表。這種能力稱為延緩名稱解析。然而，如果 Transact-SQL 預存程序、觸動程序、或批次處理參考在預存程序或觸發程序中定義的資料表，只有當相容性等級的設定值 (執行 sp_dbcmptlevel 進行設定) 等於 65 時，在建立時期才會發出警告訊息。如果使用批次處理，在編譯時期便會產生警告訊息。如果參考的資料表不存在，在執行時期將會傳回錯誤訊息。如需詳細資訊，請參閱延緩名稱解析及編譯。
權限
CREATE TRIGGER 的使用權限預設為定義觸發程序的資料表擁有者、sysadmin 固定伺服器角色、以及 db_owner db_ddladmin 固定資料庫角色的成員，並且不可轉移。
若要從資料表或檢視表擷取資料，使用者必須具有資料表或檢視表的 SELECT 陳述句使用權限。若要更新資料表或檢視表的內容，使用者必須具有資料表或檢視表的 INSERT、DELETE、及 UPDATE 陳述句使用權限。
如果檢視表上存在 INSTEAD OF 觸發程序，使用者必須具有檢視表的 INSERT、DELETE、及 UPDATE 權限，才能對檢視表發出 INSERT、DELETE、及 UPDATE 陳述式，不論是否真正在檢視表上執行這類的操作。
範例
A. 使用含有提醒訊息的觸發程序
本範例當任何人嘗試新增或變更 titles 資料表中的資料時，觸發程序將會列印訊息至用戶端。
附註 訊息 50009 是 sysmessages 中的使用者自訂訊息。如需關於建立使用者自訂訊息的詳細資訊，請參閱 sp_addmessage。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
GO
B. 使用含有提醒電子郵件訊息的觸發程序
本範例當 titles 資料表變更時，將會傳送一封電子郵件訊息給指定的人員 (MaryM)。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO
C. 使用觸發程序執行員工及作業資料表間的商業規則
因為 CHECK 條件約束只可以參考已定義資料行或資料表層級之條件約束的資料行，所以在這種情況下，任何跨資料表的條件約束或商業規則都必須定義為觸發程序。
本範例將建立一個觸發程序：當插若或更新員工作業層級時，檢查指定的員工作業層級 (job_lvls) (作為薪水的基準) 是否在在已定義的作業層級範圍內。若要取得適當的範圍，必須參考 jobs 資料表。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
SELECT @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id
JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
BEGIN
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
RAISERROR ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
END
D. 使用延緩名稱解析
本範例將建立兩個觸發程序以說明延緩名稱解析。
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig1' AND type = 'TR')
DROP TRIGGER trig1
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER trig1
on authors
FOR INSERT, UPDATE, DELETE
AS
SELECT a.au_lname, a.au_fname, x.info
FROM authors a INNER JOIN does_not_exist x
ON a.au_id = x.au_id
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig1'
-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'trig2' AND type = 'TR')
DROP TRIGGER trig2
GO
CREATE TRIGGER trig2
ON authors
FOR INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = phone
FROM authors
GO
-- Here is the statement to actually see the text of the trigger.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
ON o.id = c.id
WHERE o.type = 'TR' and o.name = 'trig2'
E. 使用 COLUMNS_UPDATED
本範例將建立兩個資料表：employeeData 資料表及 auditEmployeeData 資料表。儲存機密的員工薪水資料的 employeeData 資料表可以讓人力資源部門的成員進行修改。如果變更員工的社會安全編號 (SSN)、年薪、或銀行帳戶號碼，便會產生稽核記錄並插入 auditEmployeeData 稽核資料表中。
使用 COLUMNS_UPDATED() 函數，可以快速測試包含機密員工資訊的資料行是否有任何變更。只有嘗試偵測資料表前 8 個資料行的變更時，使用 COLUMNS_UPDATED() 才能運作。
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employeeData')
DROP TABLE employeeData
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'auditEmployeeData')
DROP TABLE auditEmployeeData
GO
CREATE TABLE employeeData (
emp_id int NOT NULL,
emp_bankAccountNumber char (10) NOT NULL,
emp_salary int NOT NULL,
emp_SSN char (11) NOT NULL,
emp_lname nchar (32) NOT NULL,
emp_fname nchar (32) NOT NULL,
emp_manager int NOT NULL
)
GO
CREATE TABLE auditEmployeeData (
audit_log_id uniqueidentifier DEFAULT NEWID(),
audit_log_type char (3) NOT NULL,
audit_emp_id int NOT NULL,
audit_emp_bankAccountNumber char (10) NULL,
audit_emp_salary int NULL,
audit_emp_SSN char (11) NULL,
audit_user sysname DEFAULT SUSER_SNAME(),
audit_changed datetime DEFAULT GETDATE()
)
GO
CREATE TRIGGER updEmployeeData
ON employeeData
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/
IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
BEGIN
-- Audit OLD record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'OLD',
del.emp_id,
del.emp_bankAccountNumber,
del.emp_salary,
del.emp_SSN
FROM deleted del
-- Audit NEW record.
INSERT INTO auditEmployeeData
(audit_log_type,
audit_emp_id,
audit_emp_bankAccountNumber,
audit_emp_salary,
audit_emp_SSN)
SELECT 'NEW',
ins.emp_id,
ins.emp_bankAccountNumber,
ins.emp_salary,
ins.emp_SSN
FROM inserted ins
END
GO
/*Inserting a new employee does not cause the UPDATE trigger to fire.*/
INSERT INTO employeeData
VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32)
GO
/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/
UPDATE employeeData
SET emp_salary = 51000
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/
UPDATE employeeData
SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M'
WHERE emp_id = 101
GO
SELECT * FROM auditEmployeeData
GO
F. 使用 COLUMNS_UPDATED 測試 8 個以上的資料行
如果必須測試資料表前 8 個資料行以外的資料行是否更新，您必須使用 SUBSTRING 函數測試 COLUMNS_UPDATED 傳回值的適當位元。本範例將測試 Northwind.dbo.Customers 資料表的資料行 3、5或 9 是否被更新。
USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
文章定位：
