How to temporarily disable triggers in DB2 Universal Database (1)

Introduction
Sometimes you want to temporarily disable a table trigger. For example, although you may need a trigger to perform daily SQL operations, you may not want to trigger those triggers when running a specific script. The standard practice is to delete a trigger and re-create it when you need it again, but it is a bit difficult if you have to track many triggers. Now, where should I save the source code of those triggers ?)

This article provides three methods to solve this problem:

• Method 1: Disable a trigger for a specific user
• Method 2: Use a framework to allow the trigger to be disabled
• Method 3: Use the SQL stored procedure maintenance trigger

Each method has its advantages and disadvantages, but we will leave this discussion at the end of the article.

Method 1: Disable a trigger for a specific user
The user identification used to execute database maintenance tasks is usually different from the user identification used for applications. This method exploits this situation. To make this method effective, you only need to select the user ID to be used when you do not want to trigger the trigger.

The SQL statement in example1.db2 script demonstrates this method.

Set

To set this example:

1. Create two tablest1Andt2. We willt1Create a sample trigger.t2Insert.

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

2. Create a trigger:

   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

This trigger is simple. When the user id returned by the USER register does not match the user administratort1The value is also insertedt2. Therefore, when you do not want to trigger a triggerADMINISTRATORConnect to execute your tasks.

Test example

1. After creating a tablet1,t2And triggerstrig1Then, connect with any user different from the ADMINISTRATOR and insert the valuet1.

   INSERT INTO db2admin.t1 VALUES (111)

2. Verify that the value has been copied to the table by the trigger.t2Medium:

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

3. Next, take the userADMINISTRATORConnect and try to insert the value again:

   INSERT INTO t1 VALUES (222)

4. Verification tablet2Not changed because the trigger is not activated:

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

Method 2: Use a framework to allow the trigger to be disabled
This section describes a trigger framework that you can use for any trigger that may need to be temporarily disabled. The framework requires trigger developers to plan and agree on this concept. However, the solution to this problem is very clear.

The SQL statement in example2.db2 script demonstrates this method.

The following describes how this mechanism works:

• Define a trigger query tabletrigger_stateIt maintains a list composed of the trigger name and status active = 'y' or 'N ')
• When defining a triggertrigger_stateThe table adds a query in the WHEN clause of the trigger) to determine whether the trigger should be activated.

Set

To set this example:

1. Create two tablest1Andt2. We willt1Create a sample trigger.t2Insert.

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

2. Createtrigger_stateTable.

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

   At first glance, you may want to includetrigschemaAndtrignameColumntrigger_statePlace a primary key in the table. Currently, we do not place any constraints on the table.

3. Assume that you wantt1Createtrig1. The first thing we need to do istrigger_stateTable registration trigger:

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

   Tip:Use all valuesUppercase, Consistent with the system directory table.

4. Next, for convenience, we will create user-defined function UDF ). When we create a trigger, its usage becomes obvious:

   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:If the query fails, the function returns a null value. Therefore, make sure thattrigger_stateTable, and pass the correct parameters when calling this function.

   As you can see, this function uses the pattern and trigger name as the inputtrigger_stateQuery the table and returnactiveValue in the column.

5. Create a trigger:

   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

   This trigger is simple. When it is enabled, insertt1Will be insertedt2. However, before activating it, it calls the UDFtrigger_enabled()To determine whether the trigger is disabled. Using this function to encapsulate this query reduces the possibility of errors, especially when many triggers need to be created.

   Tip:If your trigger has used the WHEN clause for other conditions, you only need to use the AND operator to concatenate the conditions.

Test example

1. First, we test whether the trigger works as expected:

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

2. Verifyt2The value 123 is also included because the trigger is activated:

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

3. Now we will disable this trigger:

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

4. Insert another rowt1:

   INSERT INTO db2admin.t1 values (456)

5. Now, let's determine the tablet2The trigger is disabled without modification.

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