轉自：http://www.ibm.com/developerworks/cn/data/library/techarticles/0211yip/

請注意：在閱讀本文前請先閱讀?免責聲明。

簡介

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

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

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

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

回頁首

方法 1：對特定用戶禁用觸發器

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

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

設置

要設置這個示例：

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

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

創建觸發器：

CREATE TRIGGER db2admin.trig1AFTER INSERT ON db2admin.T1REFERENCING NEW AS o FOR EACH ROW MODE DB2SQLWHEN (USER <> 'ADMINISTRATOR')BEGIN ATOMICINSERT INTO db2admin.t2 values (o.c1);END

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

測試示例

在創建了表?t1?、?t2?和觸發器?trig1?之后，以不同于 ADMINISTRATOR 的任何用戶進行連接并將值插入?t1?。

INSERT INTO db2admin.t1 VALUES (111)

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

SELECT * FROM db2admin.t2C1-----------1111 record(s) selected.

接下來，以用戶?ADMINISTRATOR?連接，并嘗試再次插入值：

INSERT INTO t1 VALUES (222)

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

SELECT * FROM db2admin.t2C1-----------1111 record(s) selected.

回頁首

方法 2：用框架來允許禁用觸發器

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

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

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

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

設置

要設置這個示例：

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

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

創建?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?表中放置一個主鍵。但是，我們稍后會在?性能優化中討論優化問題。目前，我們先不在表上放置任何約束。

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

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

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

接下來，為方便起見，我們將創建用戶定義的函數（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?列中的值。

創建觸發器：

CREATE TRIGGER db2admin.trig1AFTER INSERT ON db2admin.T1REFERENCING NEW AS oFOR EACH ROW MODE DB2SQLWHEN (db2admin.trigger_enabled('DB2ADMIN','TRIG1') = 'Y')BEGIN ATOMICINSERT INTO db2admin.t2 values (o.c1);END

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

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

測試示例

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

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

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

SELECT * FROM db2admin.t2C1-----------1231 record(s) selected.

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

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

然后將另一行插入?t1?：

INSERT INTO db2admin.t1 values (456)

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

SELECT * FROM db2admin.t2C1-----------1231 record(s) selected.

重新啟用觸發器?

要重新啟用觸發器，只要再次設置觸發器的狀態。

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

性能優化

至此，我們還沒有在 trigger_state 上創建任何唯一性約束或索引，因為有理由對此進行更徹底的討論，另外還因為演示該技術時不需要這些約束或索引。

因為?trigger_state?可能維護數百甚至數千個觸發器，所以我們希望使對該表執行查尋的開銷最小化。與其在列?trigschema?和trigname?上創建主鍵（由它又可以創建唯一性索引），不如將創建這個唯一性索引作為單獨的步驟，以便我們在該索引頁中包含名為 active 的列。從基表取回多余的字節會占用額外的 I/O，這是對資源的浪費。

以下是該索引的定義，它用關鍵字?INCLUDE來指定在這個唯一性索引中添加 active 列：

CREATE UNIQUE INDEX db2admin.trigstateIX ON db2admin.trigger_state (trigschema, trigname) INCLUDE (active)

如果使用?trigger_state?表來維護數千個觸發器，則可能希望將這個表放在它自己的表空間中，并給它指定專門的緩沖池。這樣，就可以將該查找表始終保存在內存中。嘗試確定緩沖池的大小，以便確保?trigger_state?的所有行都在內存中，但不要使緩沖池太大以至浪費內存（您可以使用命令?LIST TABLESPACES SHOW DETAIL?的輸出來幫助確定緩沖池的大小）。如果觸發器數目在幾千之內，則這種優化可能是不值得的，因為，假定?trigger_state?的行大小只有 41 字節左右（假定?trigschema?占 20 個字節，?trigname?占 20 個字節，狀態占 1 個字節），那么每 4 KB 的頁能存儲 100 個觸發器的信息。

如果您有幾千個觸發器，請記住對?trigger_state?表運行統計。

當然，另一個重要的考慮事項是，只對需要定期禁用的觸發器才使用這種技術。

回頁首

方法 3：使用 SQL 存儲過程維護觸發器

在?方法 1和?方法 2中，我們描述了禁用觸發器的方法，這樣您就不必為刪除和重建它們所引發出來的問題而擔心了。在本節中，我們提供了一種解決方案，它使用 SQL 存儲過程來封裝和管理觸發器的刪除和重建。其機制是這樣設計的：源代碼始終存在于數據庫中，因此不必跟蹤觸發器的源代碼。

以下是這種機制的工作原理：

創建三個存儲過程：

• disable_trigger()?— 禁用觸發器
• enable_trigger()?— 啟用觸發器
• show_disabled_triggers()?— 顯示所有已禁用的觸發器

創建名為?trigtool.disabled_triggers?的表，它看上去類似于?syscat.triggers?系統目錄表。這個表維護已刪除觸發器的副本。最初它是空的。

當需要禁用觸發器時，調用?disable_trigger()?，它將觸發器定義從?syscat.triggers?復制到?disabled_triggers?表，然后刪除這個觸發器。

要啟用觸發器，調用?enable_trigger()?，它從?trigtool.disabled_triggers?表重新創建觸發器。

限制：不能通過這種方法禁用代碼文本超過大約 30KB 的觸發器。

用存儲過程禁用和啟用觸發器提供了一種有別于實際刪除和重建觸發器的抽象級別。我們提供了實現該存儲過程的所有源代碼。但是，請確保閱讀后面有關我們代碼的?免責聲明。

設置

使用?trigtool?模式創建所有對象，并且腳本 example3.db2 提供了所有 DDL。

創建 32KB 頁大小的緩沖區和 32KB 頁大小的表空間。

CREATE BUFFERPOOL BP32K SIZE 1000 PAGESIZE 32KCREATE TABLESPACE TS32K PAGESIZE 32KMANAGED BY SYSTEM USING ('c:\ts32k\') BUFFERPOOL BP32K

創建?trigtool.disabled_triggers?表：

CREATE TABLE TRIGTOOL.DISABLED_TRIGGERS (TRIGSCHEMA VARCHAR(128) not null,TRIGNAME VARCHAR(128) not null,TABSCHEMA VARCHAR(128) not null,TABNAME VARCHAR(128) not null,QUALIFIER VARCHAR(128) not null,FUNC_PATH VARCHAR(254) not null,TEXT VARCHAR(31500) not null) in TS32KALTER TABLE TRIGTOOL.DISABLED_TRIGGERS ADD CONSTRAINT disabledtrig_pk PRIMARY KEY (trigschema, trigname)

以下是這個表的一些重要特性：

• 該表看起來幾乎（但并非完全）與?syscat.triggers?相同。我們只包括了需要用來重新創建觸發器的列。
• 該表是在表空間 TS32K 中創建的，該表空間是 32KB 頁大小
• 在觸發器模式與觸發器名稱上創建了主鍵約束
• TEXT 列的類型是 VARCHAR(31500)，這與?syscat.triggers?中使用 CLOB 類型的 TEXT 列不同。稍后討論原因。

創建?trigtool.show_disabled_triggers()?過程，它提供了一個顯示當前已禁用觸發器的方法。基本上，它將已禁用游標的模式和名稱作為一個游標返回給該過程的調用者，應用程序或用戶可以從命令行處理器（CLP）檢索它。以下是這個過程的源代碼：

CREATE PROCEDURE TRIGTOOL.SHOW_DISABLED_TRIGGERS ()LANGUAGE SQLRESULT SETS 1BEGINDECLARE c_triggers CURSOR WITH RETURN FORSELECT trigschema, trigname FROM TRIGTOOL.DISABLED_TRIGGERS;OPEN c_triggers;END

既然我們擁有查看已禁用觸發器的方法，我們就可以創建一個名為?trigtool.disable_trigger()?的過程，它實際復制和刪除觸發器。

CREATE PROCEDURE TRIGTOOL.DISABLE_TRIGGER (IN v_schema VARCHAR(128), IN v_name VARCHAR(128))SPECIFIC DISABLE_TRIGGERLANGUAGE SQLBEGINDECLARE SQLCODE INT DEFAULT 0;DECLARE v_stmt VARCHAR(250);DECLARE EXIT HANDLER FOR NOT FOUNDSIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='Trigger Not Found';DECLARE EXIT HANDLER FOR SQLWARNINGSIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='Unable to disable trigger';INSERT INTO TRIGTOOL.DISABLED_TRIGGERSSELECTTRIGSCHEMA, TRIGNAME, TABSCHEMA, TABNAME,QUALIFIER, FUNC_PATH, CAST(TEXT as VARCHAR(31500))FROM SYSCAT.TRIGGERSWHERE TRIGSCHEMA = v_schema and TRIGNAME = v_nameAND VALID='Y';SET v_stmt = 'DROP TRIGGER ' || v_schema || '.' ||v_name;EXECUTE IMMEDIATE v_stmt;END

該過程接收兩個參數：要禁用的觸發器的模式和名稱。

第一個操作是?INSERT，它從?syscat.triggers?表將信息復制到?trigtool.disabled_triggers?表。注：?syscat.triggers?的 TEXT 列的 CLOB 數據類型被強制轉化成了 VARCHAR(31500) 數據類型。復制完成之后，就用動態 SQL 刪除觸發器。因為沒有定義異常處理程序，所以發生的任何錯誤都將導致回滾，從而使這個操作被拒絕。為了安全，聲明一個 SQLWARNING 處理程序。這個處理程序發出 SQLEXCEPTION 信號，該信號將引起回滾。換句話說，只有在整個操作序列都完成，并不出現錯誤或警告的情況下，觸發器才會被刪除。

創建?trigtool.enable_trigger()?，它將從?trigtool.disabed_triggers?表重新創建觸發器。

CREATE PROCEDURE TRIGTOOL.ENABLE_TRIGGER (IN v_schema VARCHAR(128),IN v_name VARCHAR(128))LANGUAGE SQLBEGINDECLARE SQLCODE INT DEFAULT 0;DECLARE v_qualifier VARCHAR(128);DECLARE v_func_path VARCHAR(1000);DECLARE v_stmt VARCHAR(32672);DECLARE v_curr_qualifier VARCHAR(128);DECLARE v_curr_funcpath VARCHAR(1000);DECLARE EXIT HANDLER FOR SQLWARNINGSIGNAL SQLSTATE '80000'SET MESSAGE_TEXT = 'Error. Manual recreation required';SET v_curr_qualifier = CURRENT SCHEMA;SET v_curr_funcpath = CURRENT FUNCTION PATH;SELECT qualifier, func_path, TEXT into v_qualifier, v_func_path, v_stmtFROM TRIGTOOL.DISABLED_TRIGGERSWHERE trigschema=v_schema and trigname=v_name;SET v_func_path = 'SET CURRENT FUNCTION PATH = ' || v_func_path;EXECUTE IMMEDIATE v_func_path;SET v_qualifier = 'SET CURRENT SCHEMA = ' || v_qualifier;EXECUTE IMMEDIATE v_qualifier;EXECUTE IMMEDIATE v_stmt;DELETE FROM TRIGTOOL.DISABLED_TRIGGERSWHERE trigschema=v_schema and trigname=v_name;SET v_curr_qualifier = 'SET SCHEMA = ' || v_curr_qualifier;SET v_curr_funcpath = 'SET CURRENT FUNCTION PATH = ' || v_curr_funcpath;EXECUTE IMMEDIATE v_curr_qualifier;EXECUTE IMMEDIATE v_curr_funcpath;END

該過程（與前面那個刪除觸發器的過程類似）接收兩個參數：要啟用觸發器的模式和名稱。首先，存儲當前會話的當前模式和當前函數路徑，以便我們能夠在該過程執行完成之后恢復它。然后，從?trigtool.disabled_triggers?表檢索?qualifier, func_path和?text。

qualifier所包含的模式在最初創建觸發器時限定未限定的表和視圖。類似地，?func_path值表示在最初創建觸發器時使用的函數路徑。函數路徑用來解析觸發器定義中可能存在的未限定函數。?text列包含用來創建觸發器的原始文本。

在恢復觸發器之前，設置函數路徑和當前模式值，以便在執行觸發器文本時，對所有未限定的對象引用使用適當的限定符和函數路徑。然后，使用?text，重新創建觸發器，并?從 trigtool.disabled_triggers?刪除該觸發器的副本。您可以從代碼中看出存在 30 KB 觸發器文本大小限制的原因。EXECUTE IMMEDIATE 不支持 CLOB 類型作為參數，這就是我們必須將來自syscat.triggers?的原始文本從 CLOB 強制轉化成 VARCHAR 的原因。

最后，將當前模式和當前函數路徑恢復成它們原先的值。

就象?trigtool.disable_trigger()?一樣，整個操作序列執行期間必須不出現任何錯誤或警告，否則整套操作都會回滾。

測試示例

在把一切都設置妥當之后，可以測試觸發器的禁用和啟用了。這個測試還提供了 example4.db2 腳本。要設置這個示例：

創建下列兩個表：

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

測試該觸發器是否按預期的方式工作：

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

驗證?t2?是否也具有值 123，因為激活了觸發器。

SELECT * FROM db2admin.t2C1-----------1231 record(s) selected.

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

CALL trigtool.disable_trigger('DB2ADMIN','TRIG1')

可以通過調用?trigtool.show_disabled_triggers()?來驗證觸發器已被禁用：

CALL trigtool.show_disabled_triggers()Result set 1--------------TRIGSCHEMA TRIGNAME--------------------------- --------------------DB2ADMIN TRIG11 record(s) selected.Return Status = 0

現在，將另一行插入?t1?：

INSERT INTO db2admin.t1 values (456) SELECT * FROM db2admin.t2C1-----------1231 record(s) selected.

正如預料，表?t2?未發生改變，因為觸發器已禁用。

重新啟用觸發器?
要重新啟用觸發器，只要用模式和觸發器的名稱來調用?enable_trigger()?存儲過程，如下所示：

CALL trigtool.enable_trigger('DB2ADMIN','TRIG1')

為了簡化討論，這個示例測試沒有完整演示該存儲過程的正確性。但是，在使用與缺省情況不同的模式和函數路徑（即，用隨機挑選的?CURRENT SCHEMA和?CURRENT FUNCTION PATH專用寄存器進行限定）創建觸發器時，已經通過測試證明它工作正常。在腳本 example5.db2 中，提供了一個復雜情況下的測試用例。對于這個復雜案例的分析，我們留給讀者作為一個練習。

回頁首

結束語

本文演示了三種用于禁用和啟用觸發器的方法：通過用戶、通過查找表和通過用存儲過程管理觸發器的刪除和重新創建。每種技術都有其優缺點，而每種環境具有的不同要求，會造成某種方法比另一種優越。

表 1總結了每種方法的優缺點。

隨時歡迎您的反饋，可通過?ypaul@ca.ibm.com將反饋發送給 Paul Yip。

表 1. 比較三種禁用和啟用觸發器的方法

方法	優點	缺點
對用戶禁用	不刪除觸發器 無性能開銷 對于簡單需求很理想	必須使用特定的用戶標識以避免激活觸發器
觸發器框架	不刪除觸發器 如果正在開發新觸發器，則很理想	需要觸發器開發人員對使用框架取得一致 少量的性能開銷
存儲過程	無性能開銷 觸發器源代碼全部存在于數據庫中 通過存儲過程達到了完全的抽象 如果目前已經創建了許多觸發器，則該方法很理想	包可能需要自動重新聯編 觸發器被實際刪除 創建 SQL 存儲過程需要受支持的 C 編譯器 30KB 觸發器大小的限制

回頁首

致謝

作者要感謝 Serge Riealu 寶貴的審閱。他的建議使本文更為出色。

轉載于:https://www.cnblogs.com/millen/archive/2011/11/29/2267229.html

總結

以上是生活随笔為你收集整理的如何在 DB2 Universal Database 中暂时禁用触发器(转)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。