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?
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;
-- 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;
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.