-- 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;
1条答案
按热度按时间icomxhvb1#
您可以按原样运行它以进行检查。
| 人数|
| - -|
| 一个|
| 人数|
| - -|
| 一个|
dbfiddle link