Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    5

    Unanswered: DB2 Triggers help required...!

    Could you please help me out in finding out the solution for the below problem.

    Please find the below exact trigger which i have coded for my requirement

    CREATE TRIGGER TRIG1
    AFTER UPDATE ON INSOUDVL.STTN_V01
    REFERENCING NEW AS NAUDIT OLD AS OAUDIT
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    VALUES CASE
    WHEN NAUDIT.STTN_DELETE_MKR = 'D'
    THEN
    INSERT INTO INSOUDVL.STTT_V01
    VALUES ('D',
    OAUDIT.STTN_1141_CODE ,
    OAUDIT.STTN_EASTING ,
    OAUDIT.STTN_NORTHING ,
    OAUDIT.STTN_LAST_AMENDED ,
    OAUDIT.STTN_ISSUED ,
    OAUDIT.STTN_FULL_NAME ,
    OAUDIT.STTN_CODE_TYPE ,
    NAUDIT.STTN_DELETE_MKR ,
    OAUDIT.STTN_FIBRE_IND ,
    OAUDIT.STTN_CCITT_ID ,
    OAUDIT.STTN_NODE_NUM ,
    OAUDIT.STTN_DIST_NUM_FK ,
    OAUDIT.STTN_DMIM_DIST_FK ,
    OAUDIT.STTN_DMIM_REF_FK ,
    OAUDIT.STTN_CUST_NAME_FK ,
    OAUDIT.STTN_AMENDED_BY ,
    OAUDIT.STTN_CD_AMENDED_BY ,
    OAUDIT.STTN_CD_AMENDED ,
    OAUDIT.STTN_THROUGH_RTG ,
    OAUDIT.STTN_EQPT_LOADED ,
    OAUDIT.STTN_EQPT_SOURCE ,
    OAUDIT.STTN_COS_MAN_IND ,
    OAUDIT.STTN_CUST_SITE_ID,
    CURRENT TIMESTAMP)

    WHEN NAUDIT.STTN_DELETE_MKR = ' '
    THEN
    INSERT INTO INSOUDVL.STTT_V01
    VALUES ('U',
    OAUDIT.STTN_1141_CODE ,
    OAUDIT.STTN_EASTING ,
    OAUDIT.STTN_NORTHING ,
    OAUDIT.STTN_LAST_AMENDED ,
    OAUDIT.STTN_ISSUED ,
    OAUDIT.STTN_FULL_NAME ,
    OAUDIT.STTN_CODE_TYPE ,
    OAUDIT.STTN_DELETE_MKR ,
    OAUDIT.STTN_FIBRE_IND ,
    OAUDIT.STTN_CCITT_ID ,
    OAUDIT.STTN_NODE_NUM ,
    OAUDIT.STTN_DIST_NUM_FK ,
    OAUDIT.STTN_DMIM_DIST_FK ,
    OAUDIT.STTN_DMIM_REF_FK ,
    OAUDIT.STTN_CUST_NAME_FK ,
    OAUDIT.STTN_AMENDED_BY ,
    OAUDIT.STTN_CD_AMENDED_BY ,
    OAUDIT.STTN_CD_AMENDED ,
    OAUDIT.STTN_THROUGH_RTG ,
    OAUDIT.STTN_EQPT_LOADED ,
    OAUDIT.STTN_EQPT_SOURCE ,
    OAUDIT.STTN_COS_MAN_IND ,
    OAUDIT.STTN_CUST_SITE_ID,
    CURRENT TIMESTAMP)
    END;

    While i tried to run the above query i am getting the below error.

    DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "". SOME SYMBOLS THAT MIGHT BE
    LEGAL ARE: <IDENTIFIER>
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 502 0 0 -1 4537 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
    X'000011B9' X'00000000' SQL DIAGNOSTIC INFORMATION


    Could you please help me in finding out the problem. We are using DB2 V8 .

    I have tried searching in google for this type of sysntax but no luck.

    Thanks in Advance !!!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    VALUES CASE
    WHEN NAUDIT.STTN_DELETE_MKR = 'D'
    THEN
    INSERT INTO INSOUDVL.STTT_V01
    Syntax of VALUES statement is like:
    VALUES expression | ( expression [, expression [, ...] ] )

    No INSERT statement is allowed in the VALUES statement.

    You might be looking CASE statement, not CASE expression.

    Please see the manual ”Universal Database for z/OS SQL Reference Version 8”.

  3. #3
    Join Date
    Oct 2008
    Posts
    5
    Quote Originally Posted by tonkuma
    Syntax of VALUES statement is like:
    VALUES expression | ( expression [, expression [, ...] ] )

    No INSERT statement is allowed in the VALUES statement.

    You might be looking CASE statement, not CASE expression.

    Please see the manual ”Universal Database for z/OS SQL Reference Version 8”.
    Hi Thanks tonkuma for the quick reply.

    We have a application which was doing the changes through online screens. After implementing the triggers on the table do we see any drastic performance issues? If yes/no could you please make me understand on that area.

    Thanks in advance!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •