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

    Unanswered: How can I create this trigger?

    I have this CREATE trigger but I can't figure out what I'm doing wrong here.
    CREATE TRIGGER INSERT_ACCT_TRIGGER AFTER INSERT ON ACCOUNT 00000120
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO LOG
    VALUES (
    NEXTVAL FOR SQ4LOG,
    NEWROW.ACCT_ID,
    NEWROW.ACCT_BRAN_ID,
    NEWROW.EMP_ID,
    '0',
    NEWROW.ACCT_CNY_BAL,
    '0',
    NEWROW.ACCT_USD_BAL,
    'IN',
    CURRENT_TIMESTAMP

    )
    END
    ---------+---------+---------+---------+---------+---------+---------+---------+
    DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ";". SOME SYMBOLS THAT MIGHT
    BE LEGAL ARE: <TRIGGERED-SQL-STATEMENT>
    DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHLEX SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 27 0 0 -1 1208 502 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'0000001B' X'00000000' X'00000000' X'FFFFFFFF'
    X'000004B8' X'000001F6' SQL DIAGNOSTIC INFORMATION

    I've checked, and I don't see any semi-colon in my CREATE statement so I'm at a loss for what I'm doing wrong.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about removing BEGON ATOMIC and END?

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    When I remove them, I get this error:

    DSNT408I SQLCODE = -20100, ERROR: AN ERROR OCCURRED WHEN BINDING A TRIGGERED
    SQL STATEMENT. INFORMATION RETURNED: SECTION NUMBER : 2 SQLCODE
    -540, SQLSTATE 57001, AND MESSAGE TOKENS ZUSER13.LOG
    DSNT418I SQLSTATE = 56059 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = -550 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'FFFFFDDA' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 Version 9.1 for z/OS Codes.

    -540 THE DEFINITION OF TABLE table-name
    IS INCOMPLETE BECAUSE IT LACKS
    A PRIMARY INDEX OR A REQUIRED
    UNIQUE INDEX


    Explanation:

    An attempt was made to use table table-name in a
    CREATE or ALTER statement to define a foreign key or
    a clone table, or it was referenced in an SQL
    manipulative statement. However, the current status of
    the table is incomplete, so it cannot be used as
    specified. The definition of the table may be incomplete
    because the table was defined with a PRIMARY KEY
    clause, a UNIQUE clause, or a ROWID column with
    the GENERATED BY DEFAULT attribute. The
    definition of the table is incomplete, and it cannot be
    used until a unique index is defined for:
    v the primary key (the primary index)
    v a ROWID column
    v for each set of columns in any UNIQUE clause (the
    required unique indexes).
    An attempt was made to use the table in a FOREIGN
    KEY clause or in an SQL manipulative statement.

    System action:

    The statement cannot be executed.

    Programmer response:

    Define a primary index or a required unique index on
    the table before referencing it.

    SQLSTATE:

    57001

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If I were you, I want to try something like followings.

    a) Qualify LOG in "INSERT INTO LOG VALUES ...".
    b) Review DDL of ZUSER13.LOG.
    c) Execute the INSERT statement only and compare the result with trigger's result.
    ...

Posting Permissions

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