Microsoft SQL Server 2000 超級管理手冊(二二)

最新推荐文章于 2004-10-03 09:58:00 发布

原创最新推荐文章于 2004-10-03 09:58:00 发布 · 1.1k 阅读

·

0

·

CC 4.0 BY-SA版权

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

文章标签：

#sql server #microsoft #delete #insert #go #manager

Database 专栏收录该内容

39 篇文章

订阅专栏

本文围绕 SQL Server 2000 触发器展开，介绍了其强化功能、适用场景。详细阐述了建立触发器的多种方法，包括使用 T - SQL 语句和 SQL Server Enterprise Manager，还说明了管理及修改触发器的方式。同时给出了 AFTER、INSERT、UPDATE 等不同类型触发器的创建示例及测试方法。

22. 建立及使用觸發程序

什麼是觸發程序？

SQL Server 2000 觸發程序強化功能

何時使用觸發程序

建立觸發程序

管理觸發程序

本章總結

觸發程序是一種特殊類型的預存程序。本章將學習觸發程序的用途和使用方法，以及 Microsoft SQL 2000（T-SQL）觸發程序引進的新功能，並將示範兩種建立觸發程序的方法，分別是使用 T-SQL 陳述式，和使用 SQL Server Enterprise Manager，此外，也將學習管理及修改觸發程序。

什麼是觸發程序？
　

觸發程序（trigger） 是一種特殊的預存程序，執行特定的陳述式（UPDATE、INSERT 或 DELETE）就可以啟動觸發程序。觸發程序與其他預存程序相同，可以是由簡單，亦或是複雜的 T-SQL 陳述式組成；至於與其他預存程序不同的地方，則在於當指定的資料被修改，觸發程序即自動執行，無法依名稱以手動執行。觸發程序執行時，稱為觸動（fire）。觸發程序雖建立在現有的資料庫資料表中，但它可以存取其他資料庫的資料表和物件。觸發程序不能建立在臨時的資料表或臨時的系統資料表上，只能建立在使用者自訂資料表或自訂的檢視表中。執行觸發程序所在的資料表或檢視表，稱為觸發程序資料表（trigger table）。

觸發程序有五種類型：UPDATE、INSERT、DELETE、INSTEAD OF 和 AFTER。有了觸發程序，只要您對該表格更新、插入或刪除時，就會觸動對應的 UPDATE、INSERT 或 DELETE 觸發程序。INSTEAD OF 和 AFTER 是 SQL 2000 新增的兩項觸發程序，Instead of的原義是「取代」，INSTEAD OF觸發程序會取代插入、更新和刪除操作而執行。AFTER 觸發程序會在觸發動作之後再觸動，可視為控制觸發程序啟動時間的機制。

對資料的更新、插入及刪除被視為資料修改事件。您可以設計當一項或多項修改事件產生時，即觸動觸發程序。例如，當執行 UPDATE 或 INSERT 陳述式時即觸動觸發程序。這種類型的觸發程序稱為 UPDATE/INSERT 觸發程序。您也可以建立任何一項修改事件產生時，執行相對的 UPDATE/INSERT/DELETE 觸發程序。

下面是關於觸發程序的一些其他規定：

觸發程序只在觸發它的陳述式完成後執行。舉例來說，如果 UPDATE 陳述式成功，UPDATE 觸發程序才會被觸動。
　
如果陳述式在資料表中執行違反條件約束或引起錯誤，觸發程序不會觸動。
　
觸發程序視為單一交易中的一部份，因此可以由原觸發程序復原交易，如果在交易過程中偵測到嚴重的錯誤（如使用者中斷連線），則會自動復原整個交易。

一個陳述式只能觸動一次觸發程序。
　
當觸發程序觸動，若產生任何結果，就會如預存程序一樣，將結果傳回其呼叫的應用程式。一般來說，INSERT、UPDATE 或 DELETE 的陳述式（觸動觸發程序的陳述式）不會將結果傳回；結果通常由 SELECT 查詢傳回。因此，為了避免觸發程序傳回結果給應用程式，請勿在觸發程序定義中引入 SELECT 陳述式或指派變數。如果希望從觸發程序中傳回結果，在允許修改觸發資料表的每個應用程式中都必須撰寫特殊的程式，才能使應用程式收到傳回的資料並進行正確的處理。

如果您必須在觸發程序中指派變數，則可在觸發程序的起始位置使用 SET NOCOUNT ON 陳述式以防止傳回任何結果資料列。SET NOCOUNT 陳述式指定是否傳回查詢或受陳述式影響的資料列數目的資訊（例如，影響 23 個資料列）。SET NOCOUNT 的預設值是設在 OFF，也就是說會傳回受影響列的訊息。該設定並不影響 SELECT 陳述式實際結果的傳回，只傳回計數。

SQL Server 2000 觸發程序強化功能
　

SQL Sever 2000 新增了 INSTEAD OF 及 AFTER 兩項觸發程序。INSTEAD OF 觸發程序可以替代觸發的 SQL 敘述而執行，您只能對每個陳述式（INSERT、UPDATE 與 DELETE）定義一個 INSTEAD OF 觸發程序。INSTEAD OF 可以定義於一個資料表或是檢視表中。若是數個檢視表中都有 INSTEAD OF 觸發程序，則可以在檢視表中藉由定義檢視表將 INSTEAD OF 觸發程序串聯（cascade）。若是在可更新檢視表中含有 WITH CHECK 選項，則無法使用 INSTEAD OF 觸發程序。若在可更新檢視表中定義 INSTEAD OF 觸發程序，則必須用 ALTER VIEW 命令將 WITH CHECK 選項從可更新檢視表中移除。有關建立檢視表的詳細資訊，請參閱本書第十八章。

AFTER 觸發程序會在觸發 SQL 陳述式中 所有的 動作順利完成之後才觸動，引起參考串聯（referential cascade）及條件約束檢查（constraint checks）動作。您可以為每個觸發動作（INSERT、UPDATE 或 DELETE）指定多個 After 觸發程序。如果資料表有多個 After 觸發程序的話，可以使用sp_settriggerorder定義哪個 After 觸發程序先觸動，哪個最後觸動。除了頭尾兩個 After 觸發程序外，所有其他觸發程序觸動的順序無法由您控制。

除了新增的觸發程序外，SQL Server 2000 允許在資料表或檢視表中定義觸發程序。之前的版本僅允許在資料表中定義觸發程序。檢視表中的觸發程序的功能定義和在資料表中的功能定義相同。

何時使用觸發程序
　

觸發程序和條件約束相同，可用來維持資料的完整性和商業規則，但是觸發程序不能取代條件約束（條件約束在本書第十六章有討論）。例如，您不需要建立觸發程序來檢查資料表中主索引鍵中的某個值是否存在，才能決定這個值是否能被插入到另一個資料表中的相對應資料行（外部索引鍵條件約束在這種情況下才是一個較好的選擇）。您應當建立一個觸發程序來啟動資料庫中所有相關資料表的串聯變更，例如，您可能在 pubs 資料庫中titles這個資料表中的title_id資料行上建立 DELETE 觸發程序，當您在titles欄位中刪除一筆資料時，在sales、roysched和titleauthor資料表中對應資料的資料行也會被刪除（在接下來的章節我們將看到如何建立該 DELETE 觸發程序）。

您還可以利用觸發程序執行比 CHECK 條件約束更複雜的資料檢測（CHECK 條件約束在第十六章有詳細討論）。由於觸發程序可以引用其他資料表中的資料行，因此才可能執行複雜的資料檢測；反之，CHECK 條件約束只限於在其所定義的資料表上執行。

您還可以建立多重觸發程序，當資料修改時即觸動所有觸發程序。（請記住，如果在資料表或檢視表中為一個事件定義多重觸發程序，每個觸發程序都必須有一個自己的名稱）。

或者您可以建立單一觸發程序，在資料修改時即被觸動。也就是每一次當被定義的事件發生，觸發程序就被觸動一次。因此，若是在資料表上定義 INSERT、UPDATE 和 DELETE 的觸發程序，每次定義的事件產生時，觸發程序就會觸動。

在建立觸發程序時，SQL Server 會為觸發程序建立兩個暫時資料表，您可以參考這兩個資料表，用 T-SQL 撰寫觸發程序定義。這些資料表固定儲存在與觸發程序一起的記憶體中，每個觸發程序只能存取自己的暫時資料表，暫時資料表即為觸發程序所在資料表的一個副本。您可以使用這些資料表比較資料修改前後狀態。下一節將列舉這些特殊資料表（稱為deleted和inserted資料表）。
建立觸發程序
　

認識了觸發程序以及用途，現在來了解建立觸發程序的細節。首先，我們會用 T-SQL 建立觸發程序，然後再學習利用 Enterprise Manager 來建立。利用 Enterprise Manager 建立觸發程序，也必須了解 T-SQL 撰寫程序，這和利用 Enterprise Manager 建立其他預存程序的方法是一樣的。

CREATE TRIGGER 陳述式
　

要利用 T-SQL 建立觸發程序，需要使用 CREATE TRIGGER 這個陳述式，以下為 CREATE TRIGGER 陳述式的基本語法：
```
CREATE TRIGGERtrigger_name
ON {table | name}
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF}
  {[DELETE] [,] [UPDATE] [,]}
    [WITH APPEND]
    [NOT FOR REPLICATION]
AS
    sql_statement[....n]
```
如您所視，您可以為 INSERT、UPDATE、DELETE、INSTEAD OF 或 AFTER 陳述式建立單一或一組觸發程序。如果您使用 FOR 子句，則必須指定至少一個陳述式選項，該子句會指明在資料表內，哪種資料修改事件類型才可引起觸發程序的觸動。

當觸發程序被呼叫時，會執行 AS 關鍵字後的 SQL 陳述式或陳述式集合，也可包含程式化結構，例如 IF 和 WHILE。以下所列出的陳述式，在觸發程序定義中是不允許的：
ALTER DATABASE
　
CREATE DATABASE
　
DISK INIT
　
DISK RESIZE
　
LOAD DATABASE
　
LOAD LOG
　
RECONFIGURE
　
RESTORE DATABASE
　
RESTORE LOG
　
使用deleted和inserted資料表
　

之前曾提到，當建立觸發程序時，您可以存取兩個特定的資料表。這兩個資料表雖被稱為資料表，其實並不同於一般的資料庫資料表，它們儲存在記憶體中，而非在磁碟上。這兩個資料表被命名為deleted和inserted。

兩個資料表的結構類似（相同的資料列及同一類型的資料）於定義觸發程序的資料表。deleted資料表會儲存因 DELETE 及 UPDATE 陳述式而受影響的資料列副本。當資料列因觸發程序被刪除或更新時，被刪除或更新的資料列會傳送到delete資料表；在觸發程序中即可使用deleted資料表。inserted資料表會儲存被 INSERT 及 UPDATE 陳述式影響的資料列副本，在插入或更新交易時，新的資料列會同時被加至觸發程序資料表與inserted資料表。由於執行 UPDATE 陳述式時，會被視為插入或刪除交易，舊的資料列值會保留一份副本在deleted資料表中，而新的資料列值的副本則保留在觸發程序資料表與inserted資料表。

當執行 INSERT 陳述式時，新增的資料列不會被傳送到deleted資料表，當 INSERT 陳述式啟動後去檢查 deleted 資料表的內容，資料表中雖不會保有任何副本，卻不會發生錯誤訊息。相同的，當執行 DELETE 陳述式時，刪除的資料列也不會傳到inserted資料表。所以，如欲檢視資料修改後的狀態，請參考正確的資料表。

說明

inserted和deleted資料表中的值只限於在觸發程序中使用。一旦觸發程序完成，就無法再使用這兩個資料表。

建立第一個觸發程序
　

想要看觸發程序如何運作，先建立一個包含觸發程序的簡單資料表，並定義成當資料更新時，會列印一個陳述式。以下是建立這個資料表的 T-SQL：
```
USE MyDB
GO
CREATE TABLE Bicycle_Inventory
(
       make_name       char(10)     NOT NULL,
       make_id         tinyint      NOT NULL,
       model_name      char(12)     NOT NULL,
       model_id        tinyint      NOT NULL,
       in_stock        tinyint      NOT NULL,
       on_order        tinyint      NULL,
)
GO

IF EXISTS (SELECT name
           FROM    sysobjects
           WHERE   name = "Print_Update" AND
                   type = "TR")
DROP TRIGGER Print_Update
GO

CREATE TRIGGER Print_Update
ON Bicycle_Inventory
FOR UPDATE
AS
PRINT "The Bicycle_Inventory table was updated"
GO
```
為了測試我們的觸發程序，加入資料列後更新資料表：
```
INSERT INTO Bicycle_Inventory VALUES ("Trek", 1, "5500", 5, 1, 0)
GO

UPDATE  Bicycle_Inventory
SET     make_id = 2 
WHERE   model_name = "5500"
GO
```
譯註

如果您發現執行上述 SQL 陳述式時發生錯誤，您可以在建立觸發程序之前加上「Set Quoted_Identifier OFF」陳述式以關閉 使用引號識別項功能 。

因為觸發程序被 UPDATE 啟動，所以傳回了「The Bicycle_Inventory table was updated」訊息。在這個範例中，我們將觸發程序定義為當觸發程序執行後即顯示訊息。在某些特定的情況，可以要求觸發程序傳回輸出結果，例如當您建立一個 UPDATE 觸發程序，定義為當指定的資料列接收特定的值才觸動，但是卻擔心資料的更新並不正確。此時若是有要求觸發程序回傳輸出結果（在觸發程序中加入 PRINT 陳述式），即可從結果中找出可能的錯誤；否則，一般來說您無須要求觸發程序回傳輸出結果。
建立 DELETE 觸發程序
　

現在來看一個更複雜的範例：利用 DELETE 陳述式，啟動資料表的串聯更新。我們將建立一個觸發程序，當從titles資料表中刪除資料列時，在 pubs 資料庫內其他的資料表（如sales、roysched和titleauthor），與該資料相關的資料列都會一併刪除。我們會利用 deleted 資料表指出哪些資料列該從相關資料表中刪除。（當資料列自觸發程序資料表中刪除時，即在deleted資料表中會有刪除資料的副本，所以您可以到deleted資料表檢視所有被觸發程序所刪除的資料列）。要使觸發程序運作，我們必須刪除titleauthor、roysched和sales資料表中的外部索引鍵條件約束，因為這些資料表的外部索引條件約束會參考title_id列中的資料，若不刪除外部索引鍵條件約束，觸發程序會在刪除title_id列時產生錯誤。

說明

如果您不介意 pubs 資料庫被修改，可試著自行刪除資料庫中的外部索引鍵條件約束，然後建立觸發程序。利用 Enterprise Manager 的資料庫圖表刪除外部索引鍵條件約束是最簡單的方法（在第十六章曾經說明）。請確認已刪除在其他資料表中，參考title_id列資料的外部索引鍵條件約束。

本觸發程序的 T-SQL 如下所示：
```
USE pubs
GO
IF EXISTS (SELECT name
           FROM    sysobjects
           WHERE   name = "Delete_Title" AND
                   type = "TR")
DROP TRIGGER Delete_Title
GO

CREATE TRIGGER Delete_Title
ON titles
FOR DELETE
AS
DELETE  sales
FROM    sales, deleted
WHERE   sales.title_id = deleted.title_id
PRINT   "Deleted from sales"
DELETE  roysched
FROM    roysched, deleted
WHERE   roysched.title_id = deleted.title_id
PRINT   "Deleted from roysched"
DELETE  titleauthor
FROM    titleauthor, deleted
WHERE   titleauthor.title_id = deleted.title_id
PRINT   "Deleted from titleauthor"
GO
```
要測試觸發程序，請使用以下的 DELETE 陳述式：
```
DELETE  titles
WHERE   title_id = "PC1035"
GO
```
假設您已刪除稍早提及的外部索引鍵，一旦執行這個 DELETE 陳述式，觸發程序便會觸動，您會在title資料表上看到資料修改事件影響列的訊息，接著是從觸發程序中三行 PRINT 陳述式說明的訊息，並列出每個資料表中受影響的列數，輸出結果如下：
```
(影響 1 個資料列)

Deleted from sales

(影響 5 個資料列)

Deleted from roysched

(影響 1 個資料列)

Deleted from titleauthor

(影響 1 個資料列)
```
deleted資料表的另一個用途，是將資料表中被刪除的資料列儲存到備份資料表中，以供日後的資料分析。例如，您可以利用以下的語法，從roysched資料表將被刪除的資料列存入一個命名為roysched_backup的資料表中：
```
USE pubs
GO
CREATE TABLE roysched_backup
(
     title_id      tid  NOT NULL,
     lorange       int  NULL,
     hirange       int  NULL,
     royalty       int  NULL
)

CREATE TRIGGER tr_roysched_backup
ON roysched
FOR DELETE
AS
INSERT INTO roysched_backup SELECT * FROM deleted
GO

SELECT * FROM roysched_backup
GO
```
請注意，我們把備份資料表的資料行名稱與資料型態，設定成和來源資料表相同。您可以自行命名資料行，但是兩個資料表的資料型態應該相同，以確保資料表間的相容性。

建立 INSERT 觸發程序
　

在這個範例中，我們將建立一個 INSERT 觸發程序（當 INSERT 陳述式執行時，會讓此觸發程序觸動），當我們在sales資料表的qty資料列中插入新值，觸發程序會更新titles資料表中的ytd_sales資料列。觸發程序查詢inserted資料表以獲得剛剛插入sales資料表的qty值。我們在可在觸發程式內引用 SELECT 陳述式以顯示inserted資料表中所含的資料。

以下是該觸發程序的 T-SQL：
```
USE pubs
GO
IF EXISTS (SELECT  name
           FROM    sysobjects
           WHERE   name = "Update_ytd_sales" AND
                   type = "TR")
DROP TRIGGER Update_ytd_sales
GO

CREATE TRIGGER Update_ytd_sales
ON sales
FOR INSERT
AS
SELECT *
FROM    inserted
UPDATE  titles
SET     ytd_sales = ytd_sales + qty
FROM    inserted
WHERE   titles.title_id = inserted.title_id
GO
```
您可以注意到，我們在 UPDATE 陳述式中使用 FROMtable_source（FROM inserted）子句來指明qty值來源於inserted資料表。請執行下面的 INSERT 陳述式，以檢視來自該觸發程序的結果：
```
INSERT INTO sales VALUES(7066, 1, "2000-03-07", 100, "Net 30", 
"BU1111")
GO
```
您將看到如下的結果。第一組結果顯示 inserted 資料表中所選擇的資料列，然後顯示（影響 1 個資料列）訊息（來自 UPDATE 陳述式），如下所示：
```
stor_id ord_num  ord_date                 qty  payterms  title_id
------- -------- -----------------------  ---- --------  --------
7066    1        2000-03-07 00:00:00.000  100  Net 30    BU1111

(影響 1 個資料列)

(影響 1 個資料列)
```
建立 UPDATE 觸發程序
　

接下來我們將建立一個 UPDATE 觸發程序，當更新titles資料表中的price資料行時，該觸發程序會檢查price資料行，並驗證價格上漲不超過 10%。如果超過了 10%，將以 ROLLBACK 陳述式來復原觸發程序和呼叫觸發程序的陳述式。如果在一個較大的交易中啟動觸發程序，整筆交易將被復原。在這個範例中，我們利用deleted和inserted資料表來測試價格的變化。該觸發程序定義如下所示：
```
USE pubs
GO
IF EXISTS (SELECT  name
           FROM    sysobjects
           WHERE   name = "Update_Price_Check" AND
                   type = "TR")
DROP TRIGGER Update_Price_Check
GO

CREATE TRIGGER Update_Price_Check
ON titles
FOR UPDATE
AS
DECLARE @orig_price money, @new_price money
SELECT  @orig_price = price from deleted
PRINT   "orig price ="
PRINT   CONVERT(varchar(6）, @orig_price)
SELECT  @new_price = price from inserted
PRINT   "new price ="
PRINT   CONVERT(varchar(6), @new_price)
IF(@new_price > (@orig_price * 1.10))
BEGIN
    PRINT "Rollback occurred"
    ROLLBACK
END
ELSE
PRINT "Price is OK"
END
GO
```
執行以下的陳述式，來檢視一本title_id為BU1111的價格，測試該觸發程序：
```
SELECT  price
FROM    titles
WHERE   title_id = "BU1111"
GO
```
價格是 $11.95。接下來，執行以下陳述式，將價格增加 15%：
```
UPDATE  titles
SET     price = price * 1.15
WHERE   title_id = "BU1111"
GO
```
您會看到以下的結果：
```
orig price =
11.95
new price =
13.74
Rollback occurred
```
觸發程序觸動，列印出原價格及新價格，但由於價格增加超過 10%，因此將此交易復原。

現在檢查價格，確認是否將修改回復。請使用下面的 T-SQL：
```
SELECT  price
FROM    titles
WHERE   title_id = "BU1111"
GO
```
因價格重設至 $11.95（而非新設至 $13.75），所以修改確實被回復。現在，我們來測試若價格修改低於 10%，價格是否會被更新。按 10% 修改價格，用以下 T-SQL 測試結果：
```
UPDATE  titles
SET     price = price * 1.09
WHERE   title_id = "BU1111"
GO

SELECT  price
FROM    titles
WHERE   title_id = "BU1111"
GO
```
價格已更新至 $13.03，由於增加少於 10%，觸發程序不會被回復。

當建立一個 UPDATE 觸發程序時，可以指定觸發程序僅在特定的資料列更新時，才執行陳述式。例如，您可以設定只有在price資料行被更新時，才檢查該資料行，確認觸發程序正確觸發。請使用下面的 IF UPDATE 子句：
```
USE pubs
GO
IF EXISTS (SELECT  name
           FROM    sysobjects
           WHERE   name = "Update_Price_Check" AND
                   type = "TR")
DROP TRIGGER Update_Price_Check
GO

CREATE TRIGGER Update_Price_Check
ON titles
FOR UPDATE
AS
IF UPDATE (price)
BEGIN
   DECLARE @orig_price money, @new_price money
   SELECT  @orig_price = price from deleted
   PRINT   "orig price ="
   PRINT   CONVERT(varchar(6), @orig_price)
   SELECT  @new_price = price from inserted
   PRINT   "new price ="
   PRINT CONVERT(varchar(6), @new_price)
   IF(@new_price > (@orig_price * 1.10))
   BEGIN
       PRINT "Rollback occurred"
       ROLLBACK
   END
   ELSE
   PRINT "Price is OK"
END
GO
```
如果titles資料表的更新不包括price資料行（僅更新其他資料行），觸發程序會跳過第一個 BEGIN 和 END 之間的陳述式，也跳過該觸發程序。

要測試以上的觸發程序是否如預期般跳過執行 BEGIN 和 END 之間的陳述式，您可以用下面的 T-SQL 陳述式，更新一個非price資料行的資料。以下的例子是將title_id為 BU1111 的這本書，更新其tyd_sales資料行的值。
```
UPDATE  titles
SET     ytd_sales = 123
WHERE   title_id = "BU1111"
GO
```
您可以看到資料行的更新不在price資料行，外部 IF 條件回傳 FALSE，因此輸出結果不啟動觸發程序的列印陳述式。利用這種方式可以避免 SQL Server 處理不必要的陳述式。

建立 INSTEAD OF 觸發程序
　

INSTEAD OF 觸發程序讓您能控制 INSERT、UPDATE 及 DELETE 陳述式執行時所發生的事件，主要適用於聯合檢視表的更新。一般而言，聯合檢視表是不可更新的，因為 SQL Server 不知道要更新哪些基底資料表。要讓檢視表支援更新，您可以在檢視表中定義 INSTEAD OF 觸發程序，以透過檢視表修改多個基底資料表。請參照以下範例：

以下範例透過使用 T-SQL 陳述式，建立一個命名為TitlesByAuthor的檢視表，這個檢視表會參考author、titles和titleauthor資料表的資料。（本書第十八章可找到更多關於檢視表的資料。）
```
USE pubs
GO

CREATE VIEW TitlesByAuthor
AS
SELECT  authors.au_id, authors.au_lname, titles.title
FROM    authors INNER JOIN
        titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN
        titles ON titleauthor.title_id = titles.title_id
GO
```
建立檢視表後，我們利用 T-SQL 來顯示所有達到檢視資格的資料列：
```
USE pubs
GO

SELECT  *
FROM    TitlesByAuthor
GO

au_id        au_lname        title
-----------  --------------  --------------------------------------
238-95-7766  Carson          But Is It User Friendly?
724-80-9391  MacFeather      Computer Phobic AND Non-Phobic 
Individuals:
756-30-7391  Karsen          Computer Phobic AND Non-Phobic 
Individuals:
267-41-2394  O'Leary         Cooking with Computers: 
724-80-9391  MacFeather      Cooking with Computers:
486-29-1786  Locksley        Emotional Security: A New Algorithm
648-92-1872  Blotchet-Halls  Fifty Years in Buckingham Palace 
Kitchens
899-46-2035  Ringer          Is Anger the Enemy?
998-72-3567  Ringer          Is Anger the Enemy?
998-72-3567  Ringer          Life Without Fear
486-29-1786  Locksley        Net Etiquette
807-91-6654  Panteley        Onions, Leeks, and Garlic:
172-32-1176  White           Prolonged Data Deprivation: Four Case 
Studi
427-17-2319  Dull            Secrets of Silicon Valley
846-92-7186  Hunter          Secrets of Silicon Valley
712-45-1867  del Castillo    Silicon Valley Gastronomic Treats
274-80-9391  Straight        Straight Talk About Computers
267-41-2394  O'Leary         Sushi, Anyone?
472-27-2349  Gringlesby      Sushi, Anyone?
672-71-3249  Yokomoto        Sushi, Anyone?
213-46-8915  Green           The Busy Executive's Database Guide
409-56-7008  Bennet          The Busy Executive's Database Guide
722-51-5454  DeFrance        The Gourmet Microwave
899-46-2035  Ringer          The Gourmet Microwave
213-46-8915  Green           You Can Combat Computer Stress!

（影響 25 個資料列）
```
如果您試圖在au_lname資料行中刪除Carson，會出現下列訊息：
```
伺服器：訊息 4405，層級 16，狀態 1，行 1
由於此修改會影響多個基底資料表，檢視表或函數 'TitlesByAuthor' 無法更新。
```
要避免上述訊息產生，現在我們用 INSTEAD OF 觸發程序來執行刪除的動作，以下我們會利用一個命名為Delete_It的 INSTEAD OF 觸發程序來執行刪除的動作，其 T-SQL 的操作如下：

說明

以下的操作方式並非真正在author資料表中刪除資料列，而只是重新命名這個資料列。
```
USE pubs
GO
IF EXISTS (SELECT  name
           FROM    sysobjects
           WHERE   name = 'Delete_It' AND
                   type = 'TR')
DROP TRIGGER Delete_It
GO

CREATE TRIGGER Delete_It
ON TitlesByAuthor
INSTEAD OF DELETE
AS
PRINT   'Row from authors before deletion...'
SELECT  au_id, au_lname, city, state
FROM    authors
WHERE   au_lname = 'Carson'
PRINT   'Deleting row from authors...'
UPDATE  authors
SET     au_lname = 'DELETED'
WHERE   au_lname = 'Carson'
PRINT   'Verifying deletion...'
SELECT  au_id, au_lname, city, state
FROM    authors
WHERE   au_lname = 'Carson'
GO
```
利用以上的方式，即利用 T-SQL 陳述式自檢視表中刪除Carson，這個程序使 INSTEAD OF 觸發程序觸動，得到以下的輸出結果：
```
Row from authors before deletion...
au_id        au_name         city                  state
-----------  --------------- --------------------  -----
238-95-7766  Carson          Berkeley              CA

(影響 1 個資料列）
Deleting row from authors...
(影響 1 個資料列)
Verifying deletion...
au_id        au_name         city                  state
-----------  --------------- --------------------  -----

(影響 0 個資料列)
```
建立 AFTER 觸發程序
　

本章之前曾提到，AFTER 觸發程序會在 SQL 陳述式動作觸發之後才觸動。您可以為每個觸發動作（INSERT、UPDATE 或 DELETE）指定多個 After 觸發程序。如果資料表有多個 After 觸發程序的話，您可以使用sp_settriggerorder定義哪個 After 觸發程序先觸動，哪個最後觸動。除了頭尾兩個 After 觸發程序外，所有其他觸發程序觸動的順序無法由您控制。以下是這個陳述式的語法：
```
sp_settriggerorder [@triggername =]'triggername',
                   [@order=] {'first' | 'last' | 'none'}
```
首先來看個例子。假定我們在一個資料表中定義了四個觸發程序，分別命名為MyTrigger、MyOtherTrigger、AnotherTRigger和YetAnotherTrigger，當陳述式動作觸發之後，讓Another Trigger第一個觸動，MyTrigger最後一個觸動。
```
sp_settriggerorder @triggername = 'AnotherTrigger', @order = 'first'
go
sp_settriggerorder @triggername = 'MyTrigger', @order = 'last'
go
sp_settriggerorder @triggername = 'MyOtherTrigger', @order = 'none'
go
sp_settriggerorder @triggername = 'YetAnotherTrigger', @order = 'none'
go
```
上述所看到的「none」，使得介於第一個及最後一個觸發程序之間啟動的觸發程序，以隨機的順序執行。由於隨機的執行順序是依觸發程序的預設進行，因此不需特別執行sp_settriggerorder定義順序。

建立巢狀觸發程序
　

巢狀觸發程序（nested trigger） 是由其他觸發程序所啟動的觸發程序，和遞迴觸發程序不同的地方在於，巢狀觸發程序並不自行啟動，而是當修改事件產生時，才由啟動其他的觸發程序所啟動。SQL Server 2000 和 SQL Server 7.0 相同，第一個觸發程序可以啟動第二個觸發程序，第二個觸發程序接著啟動第三個觸發程序，依此類推，可以高達 32 個層級的觸發程序。在 SQL Server 2000 中，巢狀觸發程序的預設狀態設為啟用，nested triggers伺服器設定參數可用來控制觸發程序能否巢狀觸發。要停止巢狀觸發程序，可執行以下的指令：
```
sp_configure "nested triggers", 0
GO
```
將nested triggers設成0時，則不啟動巢狀觸發程序；將nested triggers設成1時，則可啟動巢狀觸發程序。

接下來是巢狀觸發程序的範例：當一本書的標題從titles資料表中刪除時，會引起巢狀觸發程序，以串聯的方式刪除相對應的資料列。在本章〈建立 DELETE 觸發程序〉一節中，我們曾建立單一的觸發程序進行資料的串聯刪除。我們要沿用之前的範例，但首先要刪除之前範例中的觸發程序避免觸動，然後建立三組觸發程序；第二及第三組觸發程序為巢狀觸發程序，語法如下：
```
USE pubs
IF EXISTS (SELECT  name
           FROM    sysobjects
                   WHERE   name = "Delete_Title" AND
                   type = "TR")
DROP TRIGGER Delete_Title
GO

CREATE TRIGGER TR_on_titles
ON titles
FOR DELETE
AS
DELETE  sales
FROM    sales, deleted
WHERE   sales.title_id = deleted.title_id
PRINT   "Deleted from sales"
GO

CREATE TRIGGER TR_on_sales
ON sales
FOR DELETE 
AS
DELETE  roysched
FROM    roysched, deleted
WHERE   roysched.title_id = deleted.title_id
PRINT   "Deleted from roysched"
GO

CREATE TRIGGER TR_on_roysched
ON roysched
FOR DELETE 
AS
DELETE  titleauthor
FROM    titleauthor, deleted
WHERE   titleauthor.title_id = deleted.title_id
PRINT   "Deleted from titleauthor"
GO
```
欲執行觸發程序，必須先刪除資料表上外部索引鍵條件約束（如本章〈建立 DELETE 觸發程序〉一節所示）。使用以下 DELETE 陳述式，測試是否正確執行所有觸發程序。
```
DELETE
FROM    titles
WHERE   title_id = "PS7777"
GO
```
您可以看到如下的結果：
```
（影響 2 個資料列）

（影響 1 個資料列）

Deleted from titleauthor

（影響 2 個資料列）

Deleted from roysched

（影響 1 個資料列）

Deleted from sales

（影響 1 個資料列）
```
當一組巢狀觸發程序的任一層級失敗，交易會被取消，且所有的資料修改將被復原至整組觸發程序觸動前的狀態。

使用 Enterprise Manager 建立觸發程序
　

要使用 Enterprise Manager 建立觸發程序，您只需要在 觸發屬性 視窗中鍵入 T-SQL 陳述式即可，執行步驟如下：
2. 從 Enterprise Manager 中，在您想要建立觸發程序的資料表名稱上按右鈕，開啟 快顯功能表 視窗。選擇 所有工作／管理觸發程序 ，就會出現 觸發屬性 對話方塊，如圖 22-1 所示。
  　
  
  圖22-1　「觸發屬性」對話方塊
3. 在文字中輸入觸發程序的 T-SQL 語法（圖 22-2）。
4. 按一下 檢查語法 按鈕以核對語法。語法如正確會顯示 語法檢查成功 。
  視窗（圖 22-3）。按一下確定以建立觸發程序。現在觸發程序 Print_Update 出現在名稱下拉式清單中，如圖 22-4 所示。
5. 觸發屬性 視窗保持開啟的狀態，使您可以在資料表上建立其他的觸發程序。如果您沒有其他的觸發程序要建立，按一下關閉。
  　
  
  圖22-2　輸入觸發程序語法後的「觸發屬性」對話方塊
  
  　
  
  圖22-3　語法檢查成功視窗
  
  　
  
  圖22-4　下拉式清單中新建立觸發程序的名稱
管理觸發程序
　

在本節中，我們先學習使用 T-SQL 管理觸發程序，然後再學習如何使用 Enterprise Manager 管理。

使用 T-SQL 管理觸發程序
　

T-SQL 指令可用於管理觸發程序。您可以檢視觸發程序程式語法、存在於特定資料表上的觸發程序、更改觸發程序語法、刪除觸發程序、啟動或停止觸發程序。本節中將會說明這些選項。

檢視觸發程序語法
　

sp_helptext和sp_helptrigger兩種系統預存程序能提供檢視觸發程序的相關資訊。若要顯示用來建立程序的文字，可在預存程式名稱之前執行sp_helptext。舉例來說，若要顯示之前建立Print_Update這個觸發程序中的文字定義，可使用下面的指令：
```
USE MyDB
GO
sp_helptext Print_Update
GO
```
輸出結果如下：
```
Text
-----------------------------
CREATE TRIGGER Print_Update
ON Bicycle_Inventory
FOR UPDATE
AS
PRINT "The Bicycle Inventory table was updated."
```
檢視資料表中現存的觸發程序
　

要檢視存在特定資料表中的觸發程序（或檢視觸發程序是否存在），可在資料表名稱之前執行sp_helptrigger預存程序。要檢視範例資料表MyTable的觸發程序，請使用下面的指令：
```
USE MyDB
GO
sp_helptrigger MyTable
GO
```
輸出結果如下：
```
trigger_name trigger_owner isupdate  isdelete  isinsert isafter isinsteadof
------------ ------------  --------- --------- -------- -------- ---------
Print_Update dbo           1         0         0        1        0

（影響 1 個資料列）
```
輸出結果顯示觸發程序名稱、觸發程序擁有者，以及啟動觸發程序的資料修改事件類型。如果觸發程序因該類型的資料修改事件啟動，輸出結果資料行isupdate、isdelete、isinsert、inafter和isinsteadof的值則為1，如果沒有啟動則為0。如果觸發程序因多種類型的資料修改事件觸動，就會有多個資料行含有值1。

使用 ALTER TRIGGER
　

要改變先前建立的觸發程序定義，可以刪除後重建觸發程序，也可以使用 ALTER TRIGGER 陳述式，這個陳述式使用和 CREATE TRIGGER 陳述式相同的語法。如果要改變觸發程序，必須重新定義整個觸發程序。舉例來說，要改變Print_Update觸發程序對Bicyle_Inventory的資料修改方式（當 INSERT 或 UPDATE 陳述式執行時即會觸動），可執行以下語法：
```
USE MyDB
GO
ALTER TRIGGER Print_Update
ON MyTable
FOR UPDATE, INSERT
AS
PRINT "Bicycle_Inventory was updated or a row was inserted"
GO
```
現在先前所定義的觸發程序已被更新過的版本所代替。現在如果在Bicycle_Inventory上執行 UPDATE 或 INSERT，觸發程序即會觸動，以下為執行的語法：
```
INSERT INTO Bicycle_Inventory VALUES ("Trek",1,"Lance S.E.",1,0,1)
GO
UPDATE  Bicycle_Inventory
SET     in_stock = 1
WHERE   model_name = "Lance S.E."
GO
```
使用 DROP TRIGGER
　

要從資料表中完全刪除觸發程序，可利用 DROP TRIGGER 陳述式。刪除的語法如下：
```
DROP TRIGGERtrigger_name
```
要刪除Pring_Update觸發程序，可使用下面的陳述式：
```
USE Bicycle_Inventory
GO
DROP TRIGGER Print_Update
GO
```
如果現在檢視存在於MyTable資料表上的所有觸發程序，會發現該觸發程序已不存在，可以使用以下語法檢視：
```
USE MyDB
GO
sp_helptrigger MyTable
GO
```
說明

如果您刪除一個資料表，該資料表上的所有觸發程序都會被自動刪除。

啟動和停止觸發程序
　

利用 ALTER TABLE 陳述式，不用刪除觸發程序即可變更觸發程序的定義，由於每個觸發程序都定義在一個特定資料表上，所以使用 ALTER TABLE 陳述式來代替 ALTER TRIGGER 陳述式。使用以下語法，重建本章中第一個範例的觸發程序：
```
USE MyDB
GO
IF EXISTS (SELECT  name
           FROM    sysobjects
           WHERE   name = "Print_Update" AND
                   type = "TR")
DROP TRIGGER Print_Update
GO

CREATE TRIGGER Print_Update
ON Bicycle_Inventory
FOR UPDATE
AS
PRINT "The Bicycle_Inventory table was updated"
GO
```
觸發程序在一開始會自動啟用。關閉觸發程序可使它不被啟用（直到重新啟用為止），但是其定義仍然存在於該資料表上。可使用下面的 DISABLE TRIGGER 選項關閉觸發程序：
```
ALTER TABLE Bicycle_Inventory
DISABLE TRIGGER Print_Update
GO
```
現在當Bicycle_Inventory上發生更新時，Print_Update觸發程序不會觸動。欲重新啟動觸發程序，可利用 ENABLE TRIGGER，語法如下：
```
ALTER TABLE Bicycle_Inventory
ENABLE TRIGGER Print_Update
GO
```
ENABLE TRIGGER 和 DISABLE TRIGGER 子句可使觸發程序定義保持不變，視需要而開啟或關閉該觸發程序即可。

使用 Enterprise Manager 管理觸發程序
　

您也可以使用 Enterprise Manager 管理觸發程序，儘管可使用圖形化使用介面管理觸發程序，仍需撰寫 T-SQL 程式語法。

刪除觸發程序
　

要使用 Enterprise Manager 刪除觸發程序，按照下面的步驟：
1. - 在資料表名稱上按右鈕叫出快顯功能表，選擇 所有工作／管理觸發程序 。
  - 從名稱下拉式清單中選擇觸發程序名稱，然後按一下刪除。
2. 當詢問您是否確定想要刪除觸發程序時，按一下確定（如圖 22-5）。
  　
  
  圖22-5　確定是否刪除觸發程序的對話方塊
修改觸發程序
　

按照以下步驟，即可使用 Enterprise Manger 修改觸發程序
1. - 在資料表名稱上按右鈕叫出快顯功能表，選擇 所有工作／管理觸發程序 。
  - 從名稱下拉式清單中選擇觸發程序名稱。
  - 在文字方塊中編輯 T-SQL 語法，使用 Ctrl+Tab 鍵來排縮觸發程序的文字。您可以使用 CREATE TRIGGER 或 ALTER TRIGGER 陳述式，不論使用其中的哪一種，SQL Server 會刪除現存的觸發程序並且使用其中一種陳述式重建觸發程序，依這樣的操作，您不會得到在使用互動式 OSQL 或ISQL 時，所會出現的錯誤訊息。完成編輯後，按一下確定，SQL Server 將會自動修改觸發程序定義。
本章總結
　

在本章中，我們學習了何謂觸發程序，並認識了五種類型的觸發程序，分別是 INSERT、UPDATE、DELETE、ALTER 和 INSTEAD OF，還學到了使用 T-SQL 和 Enterprise Manager 建立及管理觸發程序。下一章，我們將利用 ADO 及 XML，透過網際網路存取 SQL Server 2000。

评论

被折叠的条评论为什么被折叠?

到【灌水乐园】发言

查看更多评论

添加红包

成就一亿技术人!

hope_wisdom

发出的红包

实付元

使用余额支付

点击重新获取

扫码支付

钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。