Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Posts
    5

    Unanswered: Creating DB2 trigger

    I am successful in creating a trigger in DB2 with the following code and syntax.

    CREATE TRIGGER BN79IJDA.CASE_AUD
    AFTER UPDATE OF
    HNDLR,HNDLNG_BU,HNDLNG_RSO,HNDLNG_UNIT,
    COORD_EVNT_CODE,FILM_RTRVL_NUM,PALS_NUM,
    LVL_INVSTGTN,CO_RCVD_DATE,ORG_RCVD_DATE,
    CLNT_CLOSE_DATE,CASE_CLOSE_DATE,RSLTN_CODE,
    RESP_REQ_DATE,DUE_DATE,STLMNT_COST,SECUR_CODE,
    STATUS_CODE,CONVERT_CODE,ACK_DATE,ACK_MTHD_CODE,
    POLICY_IND ON BN79IJQ2.UYTCASE
    REFERENCING OLD AS O NEW AS N
    FOR EACH ROW MODE DB2SQL
    WHEN (O.HNDLR <> N.HNDLR)
    BEGIN ATOMIC
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'HNDLR',O.HNDLR,N.HNDLR,'U',
    CURRENT TIMESTAMP,N.HNDLR);
    END#


    But I am getting getting the following error message while creating a trigger with the following syntax. Basically I AM TRYING TO INCREASE THE NUMBER OF COLUMNS IN A TABLE FOR WHICH TRIGGER NEEDS TO FUNCTION.

    Following is the code for creating a trigger for a table update for multiple columns, which is producing this error.
    CREATE TRIGGER BN79IJDA.CASE_AUD
    AFTER UPDATE OF
    HNDLR,HNDLNG_BU,HNDLNG_RSO,HNDLNG_UNIT,
    COORD_EVNT_CODE,FILM_RTRVL_NUM,PALS_NUM,
    LVL_INVSTGTN,CO_RCVD_DATE,ORG_RCVD_DATE,
    CLNT_CLOSE_DATE,CASE_CLOSE_DATE,RSLTN_CODE,
    RESP_REQ_DATE,DUE_DATE,STLMNT_COST,SECUR_CODE,
    STATUS_CODE,CONVERT_CODE,ACK_DATE,ACK_MTHD_CODE,
    POLICY_IND ON BN79IJQ2.UYTCASE
    REFERENCING OLD AS O NEW AS N
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    WHEN (O.HNDLR <> N.HNDLR)
    THEN
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'HNDLR',O.HNDLR,N.HNDLR,'U',
    CURRENT TIMESTAMP,N.HNDLR);
    WHEN (O.CO_RCVD_DATE <> N.CO_RCVD_DATE)
    THEN
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'CO_RCVD_DATE',O.CO_RCVD_DATE,
    N.CO_RCVD_DATE,'U',CURRENT TIMESTAMP,N.HNDLR);
    END#


    Below mentione dis the error what I have received while creating the trigger above.

    SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED :
    SECTION NUMBER : 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS (,<IDENTIFIER>
    SQLSTATE = 56059 SQLSTATE RETURN CODE
    SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' X'00000000' SQL DIAGNOSTIC
    INFORMATION

    Can anyone throw some light onto this, it will be great help.

  2. #2
    Join Date
    Mar 2003
    Posts
    33

    Re: Creating DB2 trigger

    I think you should use if-then-elseif -clause insted of when. Somelthing like:

    create ... begin if O.HNDLR <> N.HNDLR then ...; elseif O.CO_RCVD_DATE <> N.CO_RCVD_DATE then... ; end if; end




    Originally posted by prabakaran
    I am successful in creating a trigger in DB2 with the following code and syntax.

    CREATE TRIGGER BN79IJDA.CASE_AUD
    AFTER UPDATE OF
    HNDLR,HNDLNG_BU,HNDLNG_RSO,HNDLNG_UNIT,
    COORD_EVNT_CODE,FILM_RTRVL_NUM,PALS_NUM,
    LVL_INVSTGTN,CO_RCVD_DATE,ORG_RCVD_DATE,
    CLNT_CLOSE_DATE,CASE_CLOSE_DATE,RSLTN_CODE,
    RESP_REQ_DATE,DUE_DATE,STLMNT_COST,SECUR_CODE,
    STATUS_CODE,CONVERT_CODE,ACK_DATE,ACK_MTHD_CODE,
    POLICY_IND ON BN79IJQ2.UYTCASE
    REFERENCING OLD AS O NEW AS N
    FOR EACH ROW MODE DB2SQL
    WHEN (O.HNDLR <> N.HNDLR)
    BEGIN ATOMIC
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'HNDLR',O.HNDLR,N.HNDLR,'U',
    CURRENT TIMESTAMP,N.HNDLR);
    END#


    But I am getting getting the following error message while creating a trigger with the following syntax. Basically I AM TRYING TO INCREASE THE NUMBER OF COLUMNS IN A TABLE FOR WHICH TRIGGER NEEDS TO FUNCTION.

    Following is the code for creating a trigger for a table update for multiple columns, which is producing this error.
    CREATE TRIGGER BN79IJDA.CASE_AUD
    AFTER UPDATE OF
    HNDLR,HNDLNG_BU,HNDLNG_RSO,HNDLNG_UNIT,
    COORD_EVNT_CODE,FILM_RTRVL_NUM,PALS_NUM,
    LVL_INVSTGTN,CO_RCVD_DATE,ORG_RCVD_DATE,
    CLNT_CLOSE_DATE,CASE_CLOSE_DATE,RSLTN_CODE,
    RESP_REQ_DATE,DUE_DATE,STLMNT_COST,SECUR_CODE,
    STATUS_CODE,CONVERT_CODE,ACK_DATE,ACK_MTHD_CODE,
    POLICY_IND ON BN79IJQ2.UYTCASE
    REFERENCING OLD AS O NEW AS N
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    WHEN (O.HNDLR <> N.HNDLR)
    THEN
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'HNDLR',O.HNDLR,N.HNDLR,'U',
    CURRENT TIMESTAMP,N.HNDLR);
    WHEN (O.CO_RCVD_DATE <> N.CO_RCVD_DATE)
    THEN
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'CO_RCVD_DATE',O.CO_RCVD_DATE,
    N.CO_RCVD_DATE,'U',CURRENT TIMESTAMP,N.HNDLR);
    END#


    Below mentione dis the error what I have received while creating the trigger above.

    SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT. INFORMATION RETURNED :
    SECTION NUMBER : 2 SQLCODE -104, SQLSTATE 42601, AND MESSAGE TOKENS (,<IDENTIFIER>
    SQLSTATE = 56059 SQLSTATE RETURN CODE
    SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    SQLERRD = 0 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' X'00000000' SQL DIAGNOSTIC
    INFORMATION

    Can anyone throw some light onto this, it will be great help.
    -r-

  3. #3
    Join Date
    Mar 2003
    Posts
    5

    Re: Creating DB2 trigger

    I tried with the following sysntax to crete a trigger, but it's producing the same error as I was receiving in my previous step.

    If someone can throw some light on this, it will be of great help to us.

    Thanks in advance.

    CREATE TRIGGER BN79IJDA.CASE_AUD
    AFTER UPDATE OF
    HNDLR,HNDLNG_BU,HNDLNG_RSO,HNDLNG_UNIT,
    COORD_EVNT_CODE,FILM_RTRVL_NUM,PALS_NUM,
    LVL_INVSTGTN,CO_RCVD_DATE,ORG_RCVD_DATE,
    CLNT_CLOSE_DATE,CASE_CLOSE_DATE,RSLTN_CODE,
    RESP_REQ_DATE,DUE_DATE,STLMNT_COST,SECUR_CODE,
    STATUS_CODE,CONVERT_CODE,ACK_DATE,ACK_MTHD_CODE,
    POLICY_IND ON BN79IJQ2.UYTCASE
    REFERENCING OLD AS O NEW AS N
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    IF (O.HNDLR <> N.HNDLR)
    THEN
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'HNDLR',O.HNDLR,N.HNDLR,'U',
    CURRENT TIMESTAMP,N.HNDLR);
    END-IF;
    IF (O.HNDLNG_BU <> N.HNDLNG_BU)
    THEN
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'HNDLNG_BU',O.HNDLNG_BU,N.HNDLNG_ BU,'U',
    CURRENT TIMESTAMP,N.HNDLR);
    END-IF;
    IF (O.POLICY_IND <> N.POLICY_IND)
    THEN
    INSERT INTO BN79IJDA.UYTCASE_AUDIT
    (CASE_CNTL_KEY,COLUMN_NAME,VALUE_FROM,VALUE_TO,ACT ION_CODE,
    CHANGED_DT,CHANGED_BY)
    VALUES
    (O.CASE_CNTL_KEY,'POLICY_IND',O.POLICY_IND,N.POLIC Y_IND,'U',
    CURRENT TIMESTAMP,'X114580');
    END-IF;
    END#

  4. #4
    Join Date
    Mar 2003
    Posts
    33

    Re: Creating DB2 trigger

    Did you try end if -statement instead of end-if (without line between words)?

    I'm using db2 on linux and end-if is not syntactically correct.


    Regards,
    -r-

  5. #5
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Creating DB2 trigger

    What version of DB2 are you using??

    Fernando

    Originally posted by raisa
    Did you try end if -statement instead of end-if (without line between words)?

    I'm using db2 on linux and end-if is not syntactically correct.


    Regards,

  6. #6
    Join Date
    Mar 2003
    Posts
    5

    Re: Creating DB2 trigger

    I tried this option of using end if instead of end-if. But this time I am receiving a new error.

    The error is as shown below.


    SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD IF, TOKEN <END-OF-STATEMENT> WAS EXPECTED
    SQLSTATE = 42601 SQLSTATE RETURN CODE
    SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    SQLERRD = 0 0 0 -1 2597 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000A25' X'00000000' SQL DIAGNOSTIC INFORMATION

    If some one can help us out, it will be of great help to us.

    Regards
    Praba

  7. #7
    Join Date
    Mar 2003
    Posts
    5

    Re: Creating DB2 trigger

    We are using version 7.0 of DB2 which is running in OS/390 environment.

    Regards
    Praba

  8. #8
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Creating DB2 trigger

    That´s why I asked the version.

    I´m running DB2 V7.2 on AIX 4.3.3 and it doesn´t support IF-Then-Else clause and I was wondering if the new version already had support for it.

    I tried to use the clause once, but I got the same error and the only condition clause can be used is the WHEN key-word.

    Thanks!

    Fernando

    Originally posted by prabakaran
    I tried this option of using end if instead of end-if. But this time I am receiving a new error.

    The error is as shown below.


    SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD IF, TOKEN <END-OF-STATEMENT> WAS EXPECTED
    SQLSTATE = 42601 SQLSTATE RETURN CODE
    SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
    SQLERRD = 0 0 0 -1 2597 0 SQL DIAGNOSTIC INFORMATION
    SQLERRD = X'00000000' X'00000000' X'00000000' X'FFFFFFFF' X'00000A25' X'00000000' SQL DIAGNOSTIC INFORMATION

    If some one can help us out, it will be of great help to us.

    Regards
    Praba
    Last edited by F.OHANA; 03-19-03 at 13:47.

Posting Permissions

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