IBM Support

Modification of Global Variables within triggers and functions

News


Abstract

Modification of Global Variables within triggers and functions

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > Modification of Global Variables within triggers and functions

SQL Global variables are a useful construct to achieve session specific inter-application communication
Prior to this change, triggers and functions were only allowed to reference global variables. SQLCODE = -20430 and SQLSTATE = '428GX‘ was returned to a trigger or function that attempted to modify a global variable
Example of what can be done now:
CREATE VARIABLE PRODLIB.MOST_RECENT_ORDER BIGINT DEFAULT 0
 
CREATE TRIGGER PRODLIB.INSERT_ORDER
BEFORE INSERT ON PRODLIB.ORDER_TABLE
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2ROW
IE : BEGIN ATOMIC
SET PRODLIB.MOST_RECENT_ORDER = NEW_ORDER_VALUE();
SET N.ORDER_VALUE = PRODLIB.MOST_RECENT_ORDER;
END IE
For more information about global variables, consult the SQL Reference: IBM i SQL Reference

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
14 January 2020

UID

ibm11167406