# Database Discussions > IBM DB2 >  DB2 z/os v8 - Problem:  trying to build a Trigger using CASE

## moozak

(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

----------


## moozak

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 ?

----------

