淺談SQL Server觸發器的使用

觸發器建立的代碼

Create Trigger TG_ProjectName On table1 After Update As Update table2 Set [工程名]=b.工程名 from table2 a,inserted b where a.ProjID = b.ID

關於觸發器中Inserted和Deleted的解釋。

inserted觸發器語句中使用了兩種特殊的表：deleted 表和 inserted 表。Microsoft® SQL Server 2000 自動建立和管理這些表。可以使用這兩個臨時的駐留記憶體的表測試某些資料修改的效果及設定觸發器操作的條件；然而，不能直接對錶中的資料變更。

inserted 和 deleted 表主要用於觸發器中：

◆擴充表間參考完整性。

◆在以視圖為基礎的基表中插入或更新資料。

◆檢查錯誤並基於錯誤採取行動。

◆找到資料修改前後表狀態的差異，並基於此差異採取行動。

Deleted 表用於儲存 DELETE 和 UPDATE 語句所影響的行的複本。在執行 DELETE 或 UPDATE 語句時，行從觸發器表中刪除，並傳輸到 deleted 表中。Deleted 表和觸發器表通常沒有相同的行。

Inserted 表用於儲存 INSERT 和 UPDATE 語句所影響的行的副本。在一個插入或更新交易處理中，建立行被同時添加到 inserted 表和觸發器表中。Inserted 表中的行是觸發器表中新行的副本。

更新事務類似於在刪除之後執行插入；首先舊行被複製到 deleted 表中，然後新行被複製到觸發器表和 inserted 表中。

在設定觸發器條件時，應當為引發觸發器的操作恰當使用 inserted 和 deleted 表。雖然在測試 INSERT 時引用 deleted 表或在測試 DELETE 時引用 inserted 表不會引起任何錯誤，但是在這種情形下這些觸發器測試表中不會包含任何行。

說明

如果觸發器操作取決於一個資料修改所影響的行數，應該為多行資料修改(基於 SELECT 語句的 INSERT、DELETE 或 UPDATE)使用測試(如檢查 @@ROWCOUNT)，然後採取相應的對策。

SQL Server 2000不允許AFTER 觸發器引用 inserted 和 deleted 表中的 text、ntext 或 image 列；然而，允許 INSTEAD OF 觸發器引用這些列。有關更多資訊，請參見 CREATE TRIGGER。

在 INSTEAD OF 觸發器中使用 inserted 和 deleted 表

傳遞到在表上定義的 INSTEAD OF 觸發器的 inserted 和 deleted 表遵從與傳遞到 AFTER 觸發器的 inserted 和 deleted 表相同的規則。inserted 和 deleted 表的格式與在其上定義 INSTEAD OF 觸發器的表的格式相同。inserted 和 deleted 表中的每一列都直接映射到基表中的列。

有關引用帶 INSTEAD OF 觸發器的表的 INSERT 或 UPDATE 語句何時必須提供列值的規則與表沒有 INSTEAD OF 觸發器時相同：

不能為計算資料行或具有 timestamp 資料類型的列指定值。

不能為具有 IDENTITY 屬性的列指定值，除非該列的 IDENTITY_INSERT 為 ON。當 IDENTITY_INSERT 為 ON 時，INSERT 語句必須提供一個值。 INSERT 語句必須為所有無 DEFAULT 約束的 NOT NULL 列提供值。
對於除計算資料行、識別欄位或 timestamp 列以外的任何列，任何允許空值的列或具有 DEFAULT 定義的 NOT NULL 列的值都是可選的。

當 INSERT、UPDATE 或 DELETE 語句引用具有 INSTEAD OF 觸發器的視圖時，資料庫引擎將調用該觸發器，而不是對任何錶採取任何直接操作。即使為檢視窗產生的 inserted 和 deleted 表中的資訊格式與基表中的資料格式不同，該觸發器在產生執行基表中的請求操作所需的任何語句時，仍必須使用 inserted 和 deleted 表中的資訊。
傳遞到在視圖上定義的 INSTEAD OF 觸發器的 inserted 和 deleted 表格式與為該視圖定義的 SELECT 語句的挑選清單相匹配。例如：

CREATE VIEW EmployeeNames (EmployeeID, LName, FName) AS SELECT EmployeeID, LastName, FirstName FROM Northwind.dbo.Employees

視圖的結果集有三列：一個 int 列和兩個 nvarchar 列。傳遞到在視圖上定義的 INSTEAD OF 觸發器的 inserted 和 deleted 表也具有名為 EmployeeID 的 int 列、名為 LName 的 nvarchar 列和名為 FName 的 nvarchar 列。

視圖的挑選清單還包含不直接映射到單個基表列的運算式。一些視圖運算式(如常量調用或函數調用)可能不引用任何列，這類運算式會被忽略。複雜的運算式會引用多列，但在 inserted 和 deleted 表中，每個插入的行僅有一個值。如果視圖中的簡單運算式引用具有複雜運算式的計算資料行，則這些簡單運算式也有同樣的問題。視圖上的 INSTEAD OF 觸發器必須處理這些類型的運算式。有關更多資訊，請參見視圖上 INSTEAD OF 觸發器中的運算式和計算資料行。

順便說一下，當對某張表建立觸發器後，分3種情況討論

1.插入操作(Insert)

Inserted表有資料，Deleted表無資料

2.刪除操作(Delete)

Inserted表無資料，Deleted表有資料

3.更新操作(Update)

Inserted表有資料(新資料)，Deleted表有資料(舊資料)

1. 講解SQL Server資料庫觸發器的安全隱患