Issue

This Content is from Stack Overflow. Question asked by mastermistik

I want to conditionally disabling triggers in DB2 by using global variable only for the current session, for example in a trigger like this

CREATE OR REPLACE TRIGGER update_headcount 
   AFTER INSERT ON employees
   FOR EACH ROW MODE DB2SQL 
   UPDATE hr_stats 
   SET headcount = headcount + 1;

How can I do this?

Solution

You may run it as is to check.

-- This value (TRUE) will be inherited by every session
CREATE VARIABLE GV_EMPLOYEE_TRIG_ENABLED BOOLEAN DEFAULT TRUE;
GRANT READ ON VARIABLE GV_EMPLOYEE_TRIG_ENABLED TO PUBLIC;
-- Don't forget to allow the corresponding user to change the variable's value
-- GRANT WRITE ON VARIABLE GV_EMPLOYEE_TRIG_ENABLED TO ...;
CREATE TABLE EMPLOYEES (ID INT);
CREATE TABLE HR_STATS (HEADCOUNT INT);
INSERT INTO HR_STATS (HEADCOUNT) VALUES 0;

CREATE OR REPLACE TRIGGER update_headcount 
   AFTER INSERT ON employees
   FOR EACH ROW MODE DB2SQL 
   WHEN (GV_EMPLOYEE_TRIG_ENABLED)
   UPDATE hr_stats 
   SET headcount = headcount + 1;

-- Trigger works in every session
-- if you don't touch the variable
INSERT INTO EMPLOYEES VALUES 1;
SELECT * FROM HR_STATS;

HEADCOUNT
1

-- Affects the current session only,
-- since every session has its own value of a variable
SET GV_EMPLOYEE_TRIG_ENABLED = FALSE;
-- Trigger doesn't work
INSERT INTO EMPLOYEES VALUES 1;
SELECT * FROM HR_STATS;

HEADCOUNT
1

dbfiddle link

This Question was asked in StackOverflow by mastermistik and Answered by Mark Barinstein It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.