如何在DB2 Universal Database中暫時禁用觸發器(1)

簡介
有時您會想暫時禁用表上的觸發器。例如，盡管您可能需要觸發器執行日常的 SQL 操作，但可能不希望在運行特定腳本時觸發那些觸發器。標准做法是刪除觸發器，當再次需要它時再重新創建，但如果您必須跟蹤許多觸發器，那就有點為難了。現在，我該把那些觸發器的源代碼保存到哪裡呢？）

本文提供了三種解決這個問題的方法：

• 方法 1：對特定用戶禁用觸發器
• 方法 2：用框架來允許禁用觸發器
• 方法 3：使用 SQL 存儲過程維護觸發器

每種方法都有其優缺點，但我們將這方面的 討論留到文章末尾。

方法 1：對特定用戶禁用觸發器
用來執行數據庫維護任務的用戶標識通常與用於應用程序的用戶標識不同，這種方法利用了這一情況。要使這種方法有效，您只需選擇在不希望觸發觸發器時要使用的用戶標識。

example1.db2腳本中的 SQL 向您演示了這種方法。

要設置這個示例：

1. 創建兩個表 t1 和 t2 。我們將在 t1 上創建一個樣本觸發器，它將引起對 t2 進行插入操作。

   
   CREATE TABLE db2admin.t1 (c1 int)
   CREATE TABLE db2admin.t2 (c1 int)

2. 創建觸發器：

   
   CREATE TRIGGER db2admin.trig1
   AFTER INSERT ON db2admin.T1
   REFERENCING NEW AS o 
   FOR EACH ROW MODE DB2SQL
   WHEN (USER <> 'ADMINISTRATOR')
   BEGIN ATOMIC
   INSERT INTO db2admin.t2 values (o.c1);
   END

這個觸發器很簡單。每當連接的用戶標識由 USER 專用寄存器返回的）與 ADMINISTRATOR 不匹配時，則將插入到 t1 中的值也插入到 t2 。因此，當不想觸發觸發器時，以用戶 ADMINISTRATOR進行連接以執行您的任務。

1. 在創建了表 t1 、 t2 和觸發器 trig1 之後，以不同於 ADMINISTRATOR 的任何用戶進行連接並將值插入 t1 。

   
   INSERT INTO db2admin.t1 VALUES (111)

2. 驗證該值已由觸發器復制到了表 t2 中：

   
   SELECT * FROM db2admin.t2
   
   C1
   -----------
           111
   
     1 record(s) selected.
   

3. 接下來，以用戶 ADMINISTRATOR 連接，並嘗試再次插入值：

   
   INSERT INTO t1 VALUES (222)

4. 驗證表 t2 未更改，因為觸發器未被激活：

   
   SELECT * FROM db2admin.t2
   
   C1
   -----------
           111
   
     1 record(s) selected.

方法 2：用框架來允許禁用觸發器
本節描述了一個觸發器框架，您可以將它用於任何可能需要暫時禁用的觸發器。使用框架要求觸發器開發人員進行規劃並對這種概念取得一致意見，但這樣做的結果可以得到該問題非常清晰的解決方案。

example2.db2腳本中的 SQL 向您演示了這種方法。

下面說明了這種機制的工作原理：

• 定義觸發器查找表 trigger_state ，它維護一個由觸發器的名稱和狀態active='Y' 或 'N'）所組成的列表
• 在定義觸發器時，向 trigger_state 表添加一次查尋在該觸發器的 WHEN 子句中）以確定該觸發器是否應該激活

要設置這個示例：

1. 創建兩個表 t1 和 t2 。我們將在 t1 上創建一個樣本觸發器，它將引起對 t2 進行插入操作。

   
   CREATE TABLE db2admin.t1 (c1 int)
   CREATE TABLE db2admin.t2 (c1 int)

2. 創建 trigger_state 表。

   
   CREATE TABLE db2admin.trigger_state 
   (
   trigschema VARCHAR(128) not null,
   trigname VARCHAR(30) not null, 
   active char(1) not null
   )
   

   乍一看，您很可能想在含有 trigschema 和 trigname 列的 trigger_state 表中放置一個主鍵。目前，我們先不在表上放置任何約束。

3. 假定您想要在表 t1 上創建名為 trig1 的觸發器。我們要做的第一件事情是向 trigger_state 表注冊該觸發器：

   
   INSERT INTO db2admin.trigger_state VALUES ('DB2ADMIN','TRIG1','Y')
   

   提示：對所有值都使用 大寫，與系統目錄表保持一致。

4. 接下來，為方便起見，我們將創建用戶定義的函數UDF）。當我們創建觸發器時，它的用途將變得很明顯：

   
   CREATE FUNCTION db2admin.trigger_enabled (
   v_schema VARCHAR(128), 
   v_name VARCHAR(30))
   RETURNS VARCHAR(1)
   RETURN (SELECT active FROM db2admin.trigger_state WHERE trigschema=v_schema and trigname=v_name)
   

   重要：如果查尋失敗，則這個函數返回空值。因此，確保正確填寫 trigger_state 表，並在調用這個函數時傳遞正確的參數。

   如您所見，該函數將模式和觸發器的名稱作為輸入，以在 trigger_state 表中執行查尋，並返回 active 列中的值。

5. 創建觸發器：

   
   CREATE TRIGGER db2admin.trig1
   AFTER INSERT ON db2admin.T1
   REFERENCING NEW AS o
   FOR EACH ROW MODE DB2SQL
   WHEN (db2admin.trigger_enabled('DB2ADMIN','TRIG1') = 'Y')
   BEGIN ATOMIC
   INSERT INTO db2admin.t2 values (o.c1);
   END
   

   這個觸發器很簡單。當啟用它時，插入 t1 的值也會插入到 t2 。但是，在激活它之前，它調用 UDF trigger_enabled() 來確定該觸發器是否被禁用。用該函數封裝這個查詢降低了出錯的可能性，尤其是在需要創建許多觸發器的情況下。

   提示：如果您的觸發器已經將 WHEN 子句用於其它條件，則只需用 AND 操作符將條件串到一起。

1. 首先，我們測試該觸發器是否按預期的方式工作：

   
   INSERT INTO db2admin.t1 values (123)
   DB20000I  The SQL command completed successfully.

2. 驗證 t2 也包含值 123，因為激活了觸發器：

   
   SELECT * FROM db2admin.t2
   
   C1
   -----------
           123
   
     1 record(s) selected.

3. 現在，我們將禁用該觸發器：

   
   UPDATE db2admin.trigger_state SET active='N' 
   WHERE trigschema='DB2ADMIN' and trigname='TRIG1'

4. 然後將另一行插入 t1 ：

   
   INSERT INTO db2admin.t1 values (456)

5. 現在，讓我們通過確定表 t2 未經更改來驗證觸發器已被禁用。

   
   SELECT * FROM db2admin.t2
   
   C1
   -----------
           123
   
     1 record(s) selected.