Turning On/Off triggers in DB2

Unfortunately, DB2 triggers, once defined, cannot be deactivated. That is why it is a good idea to build in a mechanism to control trigger execution without actually dropping a trigger if you do not want it to run. For example, you can define a table with two columns—the trigger name and a yes/no indicator. This table then can be used by the WHEN clause of a trigger. In the example below, the UPDATE_TABLE trigger executes only if the yes/no indicator is set to 'YES'.

db2 "create table trigger_control (trigger_name varchar(50), indicator char(3))"

CREATE UPDATE_TABLE
AFTER INSERT ON TABLE_1
REFERENCING NEW_TABLE AS NEW_TABLE1
FOR EACH STATEMENT
WHEN ('YES' IN SELECT INDICATOR
            FROM TRIGGER_CONTROL
            WHERE TRIGGER_NAME = 'TABLE_1')
BEGIN ATOMIC
   -- SQL CODE
END;