Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2004
    Posts
    12

    Question Unanswered: What's wrong with my TRIGGER ??

    Hi All,

    I have a problem to create a trigger.
    My code :

    --------------------------------------------------------------------
    CREATE TRIGGER DB2ADMIN.TRIG1
    AFTER UPDATE ON DB2ADMIN.T1
    FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
    DECLARE VAR1 NUMERIC;
    SET VAR1 = 210;
    INSERT INTO EMP_TEMP(COST)VALUES(VAR1);
    END

    -------------------------------------------------------------------

    ...and this is the error message from Command Center

    -------------------------------------------------------------------
    DB21034E El mandato se ha procesado como una sentencia de SQL porque no era

    un mandato válido para el procesador de línea de mandatos. Durante el proceso

    SQL se ha devuelto:

    SQL0104N Se ha encontrado un símbolo imprevisto "NUMERIC" a continuación de

    "ATOMIC DECLARE VAR1". Entre los símbolos esperados se puede incluir:

    "END-OF-STATEMENT". LINE NUMBER=1. SQLSTATE=42601


    -------------------------------------------------------------------


    I have installed DB2 v7 on Windows 2000.

    Thanks in advanced.

    Wil.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    NUMERIC should be INTEGER
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2004
    Posts
    12

    Unhappy I do correction, but get the same result.

    I do correction, but get the same result.

    I believe that is a problem of interpreter.

    ---------------------------------------------------------------------
    CREATE TRIGGER DB2ADMIN.TRIG1
    AFTER UPDATE ON DB2ADMIN.T1
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    DECLARE VAR1 INTEGER;
    SET VAR1 = 210;
    INSERT INTO EMP_TEMP(COST)VALUES(VAR1);
    END
    ----------------------------------------------
    DB21034E El mandato se ha procesado como una sentencia de SQL porque no era

    un mandato válido para el procesador de línea de mandatos. Durante el proceso

    SQL se ha devuelto:

    SQL0104N Se ha encontrado un símbolo imprevisto "INTEGER" a continuación de

    "ATOMIC DECLARE VAR1". Entre los símbolos esperados se puede incluir:

    "END-OF-STATEMENT". LINE NUMBER=1. SQLSTATE=42601
    -------------------------------------------------------------

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Try this:

    CREATE TRIGGER DB2ADMIN.TRIG1
    AFTER UPDATE ON DB2ADMIN.T1
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO EMP_TEMP(COST)VALUES(210);
    END


    I am assuming you have tested your insert statement and the EMP_TEMP table has only one column that requires a value.

  5. #5
    Join Date
    Sep 2004
    Posts
    12

    Red face No yet

    Hi, thanks for your help.

    Execute this code :
    ------------------------------------------------
    CREATE TRIGGER DB2ADMIN.TRIG1
    AFTER UPDATE ON DB2ADMIN.T1
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO EMP_TEMP(COST)VALUES(210);
    END
    -------------------------------------------------
    and now get the message:

    -------------------------------------------------------
    DB21034E El mandato se ha procesado como una sentencia de SQL porque no era un mandato válido para el procesador de línea de mandatos. Durante el proceso

    SQL se ha devuelto:

    SQL0104N Se ha encontrado un símbolo imprevisto "END-OF-STATEMENT" a
    continuación de "EMP(COST)VALUES(210)". Entre los símbolos esperados se puede incluir: "<delim_semicolon>". LINE NUMBER=1. SQLSTATE=42601

    -------------------------------------------------------

    And i testing with a easy trigger.

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Is your SQL terminator set to something besides the semicolon? My espanol is not so good, so its difficult to read the error message.

  7. #7
    Join Date
    Sep 2004
    Posts
    12

    Red face Mi SQL Terminator is default

    Mi SQL Terminator is default. (

    I try to change the character but I obtain the same error.

  8. #8
    Join Date
    Sep 2004
    Posts
    12

    Red face Mi SQL Terminator is default

    Mi SQL Terminator is default. (

    I try to change the character but I obtain the same error.

  9. #9
    Join Date
    Aug 2004
    Posts
    330
    Try testing your insert statement first. Make sure its syntax is correct.
    Then try the following before the CREATE TRIGGER statement:

    #SET TERMINATOR $


    At the end of your CREATE TRIGGER statement, add the $ to terminate the statement.

  10. #10
    Join Date
    Sep 2004
    Posts
    12

    Unhappy I execute the insert statement succesfully

    I execute the insert statement succesfully by separated from command center. Only is a test table.

    Im executing all the commands from command center. Remember that i working on windows 2000.

    ------------------------------------------------------------
    #SET TERMINATOR $

    CREATE TRIGGER DB2ADMIN.TRIG1
    AFTER UPDATE ON DB2ADMIN.T1
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO EMP_TEMP(COST)VALUES(210);
    END$
    -------------------------------------------------
    DB21034E El mandato se ha procesado como una sentencia de SQL porque no era un mandato válido para el procesador de línea de mandatos. Durante el proceso SQL se ha devuelto:

    SQL0104N Se ha encontrado un símbolo imprevisto "#SET TERMINATOR $" a
    continuación de "BEGIN-OF-STATEMENT". Entre los símbolos esperados se puede incluir: "<space>". SQLSTATE=42601
    ------------------------------------------------
    Thanks for your patience.

  11. #11
    Join Date
    Aug 2004
    Posts
    330
    From the command center, go to TOOLS => TOOLS SETTINGS

    On the GENERAL tab, there will be a setting for "Use statement termination character"

    You will need to change this from the semicolon to something else ($ for example)

    Place the termination character at the end of your CREATE TRIGGER statement.

    Execute the CREATE TRIGGER statement.

  12. #12
    Join Date
    Sep 2004
    Posts
    12

    Question It could be a problem of the C compiler ??

    I change caracter ';' for '$', but nothing.

    It could be a problem of the C compiler ??

    At begin i have problem with store procedure builder(SQL0104N), then install the Visual C and not have problems now.

    What aditional configuration need the command center?


    --------------------------------------
    create trigger db2admin.trig1
    after update on db2admin.t1
    for each row mode db2sql
    begin atomic
    insert into emp_temp(cost)values(210);
    end$
    -------------------------------------------
    DB21034E El mandato se ha procesado como una sentencia de SQL porque no era un mandato válido para el procesador de línea de mandatos. Durante el proceso SQL se ha devuelto:

    SQL0104N Se ha encontrado un símbolo imprevisto "END-OF-STATEMENT" a
    continuación de "emp(cost)values(210)". Entre los símbolos esperados se puede incluir: "<delim_semicolon>". LINE NUMBER=1. SQLSTATE=42601
    -------------------------------------------

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    DB2 will expect a space before values ...
    emp_temp(cost) values(210)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Sep 2004
    Posts
    12

    Red face

    I Try with space and get the same message error.

    emp_temp(cost) values(210)


Posting Permissions

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