DB2 conditionally firing triggers

mastermistik 0
Viewed 47

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?

db2 triggers
1 Answers

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

Mark Barinstein0
Related