Results 1 to 2 of 2

Thread: DB2 z/os v8 - Problem: trying to build a Trigger using CASE

  1. #1
    Join Date
    Feb 2011
    Posts
    2

    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

  2. #2
    Join Date
    Feb 2011
    Posts
    2
    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
  •