Introduction

Sometimes you have triggers on a table that you'd like to temporarily disable. For example, whereas you might require triggers for day-to-day SQL operations, you might not want those triggers to be fired when you run particular scripts. The standard practice is to drop a trigger and recreate it when it is needed again, but you may find this a bit disconcerting if you have to keep track of lots of triggers. (Now, where did I keep the source code for those triggers?)

This article presents three methods for doing this:

• Method 1: Disabling triggers for a particular user
• Method 2: Using a framework to allow disabling triggers
• Method 3: Uses SQL stored procedures to maintain triggers

Each method has its advantages and disadvantages and this discussion is left until the end.

Back to top

Method 1: Disabling triggers for a particular user

This method takes advantage of the fact that the user ID used to perform database maintenance tasks is usually different from that used for applications. For this method to work, you simply choose a user ID to use when you do not want triggers to fire.

The script named example1.db2 contains the SQL to demonstrate this method.

Setting it up

To set up this example:

1. Create two tables, t1 and t2. We will be creating a sample trigger on t1, which will cause an insert into t2.

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

   
   
2. Create this trigger:

   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

   
   

This trigger is straightforward. Whenever the user ID of the connection (returned by the USER special register) does not match ADMINISTRATOR, the value inserted into t1 is also inserted into t2. Therefore, whenever you don't want triggers to fire, connect as user ADMINISTRATOR to perform your tasks.

Let's test it

1. After creating tables t1 and t2, and the trigger trig1, connect as any user except ADMINISTRATOR and insert a value into t1.

   INSERT INTO db2admin.t1 VALUES (111)

   
   
2. Verify that the value has been copied into table t2 by the trigger:

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

   
   
3. Next, connect as user ADMINISTRATOR and attempt the insert again:

   INSERT INTO t1 VALUES (222)

   
   
4. Verify that table t2 has not changed because the trigger was not activated:

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

   
   

Back to top

Method 2: A framework to allow disabling triggers

This section describes a trigger framework, which you can adopt for any trigger that might require temporary disabling. Using a framework requires planning and agreement on the concept among your trigger developers, but the result is a very clean solution to the problem.

The script named example2.db2 contains the SQL to demonstrate this method.

The mechanism works as follows:

• Define a trigger lookup table trigger_state, which maintains a list of triggers by name and their status (active='Y' or 'N')
• When defining your triggers, add a lookup (in the WHEN clause of the trigger) to the trigger_state table to determine if the trigger should activate

Setting it up

To set up this example:

1. Create two tables, t1 and t2. We will be creating a sample trigger on t1, which will cause an insert into t2.

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

   
   
2. Create the trigger_state table.

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

   
   

   At first glance, you'd probably want to put a primary key on the trigger_state table using the trigschema and trigname columns. However, we discuss optimizations later in Optimizing for performance. For now, let's not put any constraints on the table.

3. Suppose that you want to create a trigger called trig1 on table t1. The first thing to do is register the trigger with the trigger_state table:

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

   
   

   Tip: Use uppercase for all values for consistency with the system catalog tables.

4. Next, we'll create a user-defined function (UDF) for convenience. Its purpose will become evident when we create the trigger:

   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)

   
   Important: This function returns null if the lookup fails. Therefore, ensure that you populate the trigger_state table properly and pass the correct parameters when calling this function.
   As you can see, the function takes as input a schema and trigger name to perform a lookup in the trigger_state table and returns the value in the column named active.
5. Create the trigger:

   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

   
   

   This trigger is straightforward. When enabled, whatever is inserted into t1 is also inserted into t2. Before it activates, however, it calls the UDF trigger_enabled() to determine if the trigger has been disabled. Using the function to encapsulate the query reduces the chances for error, especially if many triggers need to be created.

   Tip: If your triggers already make use of the WHEN clause for other conditions, simply use the AND operator to chain the conditions together.

Let's test it

1. First, we'll test if the trigger is working as expected:

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

   
   
2. Verify that t2 has the value 123 as well because of the trigger activation:

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

   
   
3. Now, we'll disable the trigger:

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

   
   
4. And insert another row into t1:

   INSERT INTO db2admin.t1 values (456)

   
   
5. Now let's verify that our trigger was disabled by ensuring that table t2 was not changed.

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

   
   

Re-enabling the trigger

To re-enable the trigger, simply set the state of the trigger again.

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

Optimizing for performance

So far, we have not created any unique constraints or indexes on the trigger_state table because a more thorough discussion on the topic is warranted and it was not required for demonstrating the technique.

Because trigger_state may maintain hundreds, or even thousands, of triggers, we want to minimize the overhead of performing lookups to this table. Rather than create a primary key (which in turn creates a unique index) on columns trigschema and trigname, it is better to create the unique index as a separate step so that we can include the column named active in the index page. It is a waste of resources to perform an additional I/O to fetch the additional byte from the base table.

Here is the index definition, which uses the keyword INCLUDE to specify the addition of the active column in the unique index:

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

If you have thousands of triggers to maintain using the trigger_state table, you might want to place this table in its own table space and assign it to a dedicated buffer pool. That way, the lookup table can be kept in memory all the time. Try to size your buffer pool so that all rows of trigger_state are guaranteed to be in memory, but don't make it so large that memory is wasted (You can use the output of the command LIST TABLESPACES SHOW DETAIL to help you size the buffer pool). This optimization is probably not worthwhile if you have fewer than several thousand triggers because, given that the row size of trigger_state is only about 41 bytes (assuming 20 bytes for trigschema, 20 bytes for trigname, and 1 byte for status), each 4 KB page is capable of storing information for 100 triggers.

If you have several thousand triggers, remember to run statistics on the trigger_state table.

Another important consideration, of course, is to use this technique only for triggers that require periodic disabling.

Back to top

Method 3: Using SQL stored procedures to maintain triggers

In method 1 and method 2, we described ways of disabling triggers so that you don't have to worry about the ramifications of dropping and re-creating them. In this section, we present a solution that uses SQL stored procedures to encapsulate and manage dropping and recreation of triggers. The mechanism is designed such that the source code never leaves the database and hence, there is no need to keep track of trigger source code.

The mechanism works as follows:

1. Create three stored procedures:
   • disable_trigger() - disable a trigger
   • enable_trigger() - enable a trigger
   • show_disabled_triggers() - show all disabled triggers
2. Create a table called trigtool.disabled_triggers, which looks similar to the syscat.triggers system catalog table. This table maintains copies of dropped triggers. It is initially empty.
3. When you need to disable a trigger, call disable_trigger(), which copies the trigger definition from syscat.triggers to the disabled_triggers table and drops the trigger.
4. To enable a trigger, call enable_trigger(), which re-recreates the trigger from the trigtool.disabled_triggers table.

Restrictions: Triggers with code text greater than approximately 30KB cannot be disabled by this method.

Using stored procedures to disable and enable triggers provides a level of abstraction from actually dropping and recreating triggers. We provide all the source code for implementing the stored procedures. However, please be sure to read the disclaimer below regarding our code.

Setting it up

All objects will be created using the trigtool schema, and all the DDL has been provided the script example3.db2.

1. Create a 32KB page size buffer pool and a 32KB page size tablespace.

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

   
   
2. Create the trigtool.disabled_triggers table:

   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 TS32K ALTER TABLE TRIGTOOL.DISABLED_TRIGGERS ADD CONSTRAINT disabledtrig_pk PRIMARY KEY (trigschema, trigname)

   
   

   There are some important features of this table:

   • The table looks almost, but not entirely, like syscat.triggers. We include only the columns that you need to recreate a trigger.
   • The table is created in tablespace TS32K, which is a 32 KB page size tablespace
   • A primary key constraint is created on trigger schema and trigger name
   • The type of TEXT column is VARCHAR(31500), which is different from the TEXT column of syscat.triggers, which uses the CLOB type. This is required for reasons that will be discussed later.
3. Create procedure trigtool.show_disabled_triggers(), which provides a method to display currently disabled triggers. Basically, it returns the schema and names of disabled cursors as a cursor to the procedure caller and can be retrieved by an application or by a user from the command line processor (CLP). Here is the source code for this procedure:

   CREATE PROCEDURE TRIGTOOL.SHOW_DISABLED_TRIGGERS () LANGUAGE SQL RESULT SETS 1 BEGIN DECLARE c_triggers CURSOR WITH RETURN FOR SELECT trigschema, trigname FROM TRIGTOOL.DISABLED_TRIGGERS; OPEN c_triggers; END

   
   
4. Now that we have a method to see disabled triggers, we can create a procedure to actually copy and drop a trigger, called trigtool.disable_trigger().

   CREATE PROCEDURE TRIGTOOL.DISABLE_TRIGGER ( IN v_schema VARCHAR(128), IN v_name VARCHAR(128)) SPECIFIC DISABLE_TRIGGER LANGUAGE SQL BEGIN DECLARE SQLCODE INT DEFAULT 0; DECLARE v_stmt VARCHAR(250); DECLARE EXIT HANDLER FOR NOT FOUND SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='Trigger Not Found'; DECLARE EXIT HANDLER FOR SQLWARNING SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='Unable to disable trigger'; INSERT INTO TRIGTOOL.DISABLED_TRIGGERS SELECT TRIGSCHEMA, TRIGNAME, TABSCHEMA, TABNAME, QUALIFIER, FUNC_PATH, CAST(TEXT as VARCHAR(31500)) FROM SYSCAT.TRIGGERS WHERE TRIGSCHEMA = v_schema and TRIGNAME = v_name AND VALID='Y'; SET v_stmt = 'DROP TRIGGER ' || v_schema || '.' ||v_name; EXECUTE IMMEDIATE v_stmt; END

   
   
   The procedure takes two parameters: the schema and the name of the trigger to be disabled.
   The first operation is an INSERT, which copies information from the syscat.triggers table to the trigtool.disabled_triggers table. Note that the TEXT column of syscat.triggers is cast from the CLOB data type to VARCHAR(31500) data type. Once copied, the trigger is dropped using dynamic SQL. Because no exception handler has been defined, any errors that occur will result in a rollback and the operation will be rejected. To be safe, a handler was declared for SQLWARNING. This handler signals an SQLEXCEPTION, which will then cause a rollback. In other words, the trigger will only be dropped if the entire sequence of operations can be completed without error or warnings.
5. Create trigtool.enable_trigger(), which recreates a trigger from the trigtool.disabed_triggers table.

   CREATE PROCEDURE TRIGTOOL.ENABLE_TRIGGER ( IN v_schema VARCHAR(128), IN v_name VARCHAR(128)) LANGUAGE SQL BEGIN DECLARE 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 SQLWARNING SIGNAL 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_stmt FROM TRIGTOOL.DISABLED_TRIGGERS WHERE 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_TRIGGERS WHERE 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

   
   

   This procedure, like its counterpart, accepts two parameters: the schema and the name of the trigger to be enabled. First, the current schema and current function path of the current session is stored so that we can restore it after the procedure completes its execution. Then, qualifier, func_path, and text are retrieved from the trigtool.disabled_triggers table.

   The qualifier contains the schema used to qualify unqualified tables and views when the trigger was originally created. Similarly, the func_path value represents the function path used when the trigger was originally created. The function path is used to resolve unqualified functions that may exist in the trigger definition. The text column contains the original text used to create the trigger.

   Before restoring the trigger, the function path and current schema values are set so that when the trigger text is executed, the proper qualifier and function path are used for all unqualified object references. Then, using text, the trigger is recreated and the trigger copy is deleted from trigtool.disabled_triggers. From the code, you can see why the 30 KB trigger text limit exists. EXECUTE IMMEDIATE does not support CLOB types as a parameter, which is why we had to cast the original text from syscat.triggers from CLOB to VARCHAR.

   Finally, the current schema and current function path are restored to the values to what they were previously.

   Just like trigtool.disable_trigger(), the entire sequence of operations must occur without error or warning or the entire set of operations is rolled back.

Let's test it

After you have everything in place, we can test disabling and enabling of triggers. This test is also provided the script example4.db2. To set up this example:

1. Create the following two tables:

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

   
   
2. Let's test to see that the trigger is working as expected:

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

   
   
3. Verify that t2 has the value 123 as well because of the trigger activation.

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

   
   
4. Now, we'll disable the trigger:

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

   
   
5. We can verify that the trigger has been disabled by calling trigtool.show_disabled_triggers():

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

   
   
6. Now, let's insert another row into t1:

   INSERT INTO db2admin.t1 values (456) SELECT * FROM db2admin.t2 C1 ----------- 123 1 record(s) selected.

   
   

   As expected, table t2 has not changed because the trigger was disabled.

Re-enabling the trigger
To re-enable the trigger, simply call the enable_trigger() stored procedure with the schema and trigger name, as shown here:

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

To keep the discussion simple, this example test does not fully demonstrate the correctness of the stored procedure. However, it has been tested to work when the trigger is created using a schema and function path that is different from the default (that is, qualified by a randomly chosen CURRENT SCHEMA and CURRENT FUNCTION PATH special register). A test case for the complex case has been has been provided as a script called example5.db2. We leave analysis of the complex case as an exercise for the reader.

Back to top

Summary

This article presented three methods for disabling and enabling triggers: By user, by lookup table, and by managed dropping and recreation of triggers using stored procedures. Each technique has its advantages and disadvantages and every environment has different requirements that deem one method to be better than the other.

Table 1 summarizes the advantages and disadvantages of each method.

Feedback is always welcome and should be sent to Paul Yip at ypaul@ca.ibm.com.

Table 1. Comparing three methods for disabling and enabling triggers

Acknowledgement

The authors would like to thank Serge Riealu for his invaluable review. His suggestions have made this a much better article.

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Back to top

Download

Information about download methods

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Method 1: Disabling triggers for a particular user
• Method 2: A framework to allow disabling triggers
• Method 3: Using SQL stored procedures to maintain triggers
• Summary
• Download
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers