DB2有条件地触发触发器

kxeu7u2r  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(149)

我希望通过只对当前会话使用全局变量来有条件地禁用DB2中的触发器,例如在这样的触发器中

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

我该怎么做?

icomxhvb

icomxhvb1#

您可以按原样运行它以进行检查。

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

| 人数|
| - -|
| 一个|
dbfiddle link

相关问题