Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    31

    Red face Unanswered: SQLCODE , SQLSTATE in Triggers

    I have created a trigger as follows :

    CREATE TRIGGER TEST
    AFTER INSERT ON DEPT
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL SET OPTION ALWBLK = *ALLREAD , ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *ALL , DECRESULT = (31, 31, 00) , DFTRDBCOL = *NONE ,
    DYNDFTCOL = *NO , DYNUSRPRF = *USER , SRTSEQ = *HEX
    BEGIN
    DECLARE HSQLCODE INTEGER DEFAULT 0 ;
    DECLARE HSQLSTATE CHAR ( 5 ) DEFAULT '00000' ;
    DECLARE SQLCODE INTEGER ;
    DECLARE SQLSTATE CHAR ( 5 ) ;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION , SQLWARNING , NOT FOUND VALUES ( SQLCODE , SQLSTATE ) INTO HSQLCODE , HSQLSTATE ;
    INSERT INTO DEPT_audit( LOC_LOG , DNAME_LOG , DNO_LOG,sqcd,sqstate ) VALUES ( NEWROW.LOC , NEWROW . DNAME , NEWROW . DNO , HSQLCODE , HSQLSTATE) ;
    END;

    INSERT INTO DEPT VALUE(99,'AA',99);
    After inserting a row in DEPT I get the desired result in the DEPT_audit table.

    SELECT * FROM DEPT_AUDIT;
    LOC || DNAME || DNO || SQLCODE || SQLSTATE
    99 || AA || 99 ||0 || 00000

    When i run the same insert statement again :
    INSERT INTO DEPT VALUE(99,'AA',99);
    It give me Message: [SQL0803] Duplicate key value specified, which is right.

    When i receive this error i want capture the value of sqlcode , sqlstate and insert the data in the DEPT_AUDIT table with the sqlcode value and sqlstate value.

    please suggest how can i do this..
    Last edited by ajh; 05-26-11 at 05:54.

  2. #2
    Join Date
    Apr 2011
    Posts
    31

    Red face guide please

    Anybody who would like to suggest please ?

Posting Permissions

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