-
DB2 z/os v8 - Problem: trying to build a Trigger using CASE
(DB2, z/os, v8)
hi,
i have an issue that i'm trying to reslove with a trigger. on an INSERT... i want to update one of 2 date fields based on a data switch being set to Y or N.
from what i have read, i should be able to create the trigger (using SPUFI) like this:
--#SET TERMINATOR ?
CREATE TRIGGER xxx.TEST_TRIGGER
NO CASCADE BEFORE INSERT ON xxx.Test_table
REFERENCING NEW AS NU
FOR EACH ROW MODE DB2SQL
VALUES(
CASE WHEN(NU.SPEC_PERM = 'Y')
THEN SET NU.OPTIN_DT = CURRENT_DATE
WHEN(NU.SPEC_PERM = 'N')
THEN SET NU.OPTOUT_DT = CURRENT_DATE
ELSE 0
END) ?
--#SET TERMINATOR ;
COMMIT;
i've created a few triggers... but never one where i've had to evaluate a value and, based on that value do different things. i keep getting:
DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE
-104, SQLSTATE 42601, AND MESSAGE TOKENS NU,END
DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
is there an easier way to solve this problem? any help would be appreciated.
thanks,
jj
-
i finally figured it out... so i thought i would come back and post the answer so that it may help someone else someday. here's the correct way (showing only the body of the trigger):
WHEN (NU.SPEC_PERM <> ' ')
BEGIN ATOMIC
SET NU.OPTIN_DT =
CASE
WHEN NU.SPEC_PERM = 'Y'
THEN CURRENT DATE
ELSE NULL
END,
NU.OPTOUT_DT =
CASE
WHEN NU.SPEC_PERM = 'N'
THEN CURRENT DATE
ELSE NULL
END;
END ?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|