Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9

    Unhappy Unanswered: Unexpected trigger token

    Hi,

    I am trying to create a trigger within DB" (version 7.1) which I wish to later expand to have multiple SQL and procedual statements within. The trigger I am initially trying to create is

    CREATE TRIGGER REPORTER.REP_AUDIT_INSERTING AFTER INSERT ON REPORTER.REPORTER_STATUS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    UPDATE
    REPORTER.REP_AUDIT_ACK
    SET ENDDATE = n.lastmodified,
    STATE = 1
    WHERE
    (
    ( REPORTER.REP_AUDIT_ACK.SERVERSERIAL = n.serverserial )
    AND ( REPORTER.REP_AUDIT_ACK.SERVERNAME = n.servername )
    AND ( REPORTER.REP_AUDIT_ACK.STATE = 0 )
    )
    END

    The problem is that when I try to create this trigger, I am getting the following error:

    ---
    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token "END" was found following "IT_ACK.STATE = 0 )

    )". Expected tokens may include: "<triggered_SQL_stmts>". SQLSTATE=42601
    ---

    Any help would be greatly appriciated. I've been banging my head agaist this for a few days now.

    Stuart

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Stuart,
    The problem is that you need to terminate each statement inside the trigger and then terminate the create trigger statement differently.

    Try the following:
    CREATE TRIGGER REPORTER.REP_AUDIT_INSERTING AFTER INSERT ON REPORTER.REPORTER_STATUS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    UPDATE
    REPORTER.REP_AUDIT_ACK
    SET ENDDATE = n.lastmodified,
    STATE = 1
    WHERE
    (
    ( REPORTER.REP_AUDIT_ACK.SERVERSERIAL = n.serverserial )
    AND ( REPORTER.REP_AUDIT_ACK.SERVERNAME = n.servername )
    AND ( REPORTER.REP_AUDIT_ACK.STATE = 0 )
    );
    END@

    Then use:
    db2 -td@ -vf myscript.sql

    The statements inside the BEGIN ATOMIC need to be terminated with a semicolon ; . That means that the whole statement (CREATE TRIGGER) needs to be terminated with something else. I used the at-sign @. This should do what you need.

    Andy

  3. #3
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9
    Hi Andy,

    Thanks for that.. easy to miss semicolons sometimes. But, I am still having problems. I am using the Command Center on Windows to run this SQL and am now getting the following error returned...

    ------------------------------ Command Entered ------------------------------
    CREATE TRIGGER REPORTER.REP_AUDIT_INSERTING AFTER INSERT ON REPORTER.REPORTER_STATUS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    UPDATE
    REPORTER.REP_AUDIT_ACK
    SET ENDDATE = n.lastmodified,
    STATE = 1
    WHERE
    (
    ( REPORTER.REP_AUDIT_ACK.SERVERSERIAL = n.serverserial )
    AND ( REPORTER.REP_AUDIT_ACK.SERVERNAME = n.servername )
    AND ( REPORTER.REP_AUDIT_ACK.STATE = 0 )
    ) ;
    -----------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token ")" was found following "UDIT_ACK.STATE = 0 )".

    Expected tokens may include: ")". SQLSTATE=42601
    ---

    The reason I am using the command center is that the script once finished will be redistributed and may be used either through the command center or through the command line.

    Thanks and regards,

    Stuart

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Stuart,
    Well that certainly is as odd one. Nothing like complaining that something is wrong and then suggest what is deemed wrong.

    What happens if you run it from the command line?

    If it is the same error, try just retyping the where clause replacing it.
    maybe something wierd is in between the two )) at the end.

    Andy

    Originally posted by svcook
    Hi Andy,

    Thanks for that.. easy to miss semicolons sometimes. But, I am still having problems. I am using the Command Center on Windows to run this SQL and am now getting the following error returned...

    ------------------------------ Command Entered ------------------------------
    CREATE TRIGGER REPORTER.REP_AUDIT_INSERTING AFTER INSERT ON REPORTER.REPORTER_STATUS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    UPDATE
    REPORTER.REP_AUDIT_ACK
    SET ENDDATE = n.lastmodified,
    STATE = 1
    WHERE
    (
    ( REPORTER.REP_AUDIT_ACK.SERVERSERIAL = n.serverserial )
    AND ( REPORTER.REP_AUDIT_ACK.SERVERNAME = n.servername )
    AND ( REPORTER.REP_AUDIT_ACK.STATE = 0 )
    ) ;
    -----------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token ")" was found following "UDIT_ACK.STATE = 0 )".

    Expected tokens may include: ")". SQLSTATE=42601
    ---

    The reason I am using the command center is that the script once finished will be redistributed and may be used either through the command center or through the command line.

    Thanks and regards,

    Stuart

  5. #5
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9
    Andy,

    I tried the trigger again with a different SQL statement within the body, and the following error was returned from the Command Center:

    ------------------------------ Command Entered ------------------------------
    CREATE TRIGGER REPORTER.REP_AUDIT_INSERTING AFTER INSERT ON REPORTER.REPORTER_STATUS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-17.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ;
    -----------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token ")" was found following "0, 0, 'NCOMS', 1234".

    Expected tokens may include: ")". SQLSTATE=42601
    ---

    I seem to have success on the command line version with the same SQL statement as you can see from below.:

    C:\PROGRA~1\SQLLIB\bin>db2 -td@ -vf c:\temp\mysql.db2
    CONNECT TO REPORTER

    Database Connection Information

    Database server = DB2/NT 7.1.0
    SQL authorization ID = STUART
    Local database alias = REPORTER


    CREATE TRIGGER REPORTER.REP_AUDIT_INSERT AFTER INSERT ON REPORTER.REPORTER_STATU
    S REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not nul
    l ) BEGIN ATOMIC INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-1
    7.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ; END
    DB20000I The SQL command completed successfully.
    ---

    any idears why this works from the command line as opposed to the Command Center?

    Thanks and regards,

    Stuart

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Stuart,
    Look at the Tool -> Settings for the Command Center and make sure
    that the Statement Terminater character is @.

    Andy

    Originally posted by svcook
    Andy,

    I tried the trigger again with a different SQL statement within the body, and the following error was returned from the Command Center:

    ------------------------------ Command Entered ------------------------------
    CREATE TRIGGER REPORTER.REP_AUDIT_INSERTING AFTER INSERT ON REPORTER.REPORTER_STATUS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-17.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ;
    -----------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token ")" was found following "0, 0, 'NCOMS', 1234".

    Expected tokens may include: ")". SQLSTATE=42601
    ---

    I seem to have success on the command line version with the same SQL statement as you can see from below.:

    C:\PROGRA~1\SQLLIB\bin>db2 -td@ -vf c:\temp\mysql.db2
    CONNECT TO REPORTER

    Database Connection Information

    Database server = DB2/NT 7.1.0
    SQL authorization ID = STUART
    Local database alias = REPORTER


    CREATE TRIGGER REPORTER.REP_AUDIT_INSERT AFTER INSERT ON REPORTER.REPORTER_STATU
    S REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not nul
    l ) BEGIN ATOMIC INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-1
    7.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ; END
    DB20000I The SQL command completed successfully.
    ---

    any idears why this works from the command line as opposed to the Command Center?

    Thanks and regards,

    Stuart

  7. #7
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9
    Andy,

    I changed the termination charcter to @, but I was still getting the same error unexpected token error message. I tried the trigger termination in a number of ways with ; and @ and without any termination character. Could it possible be a bug with the 7.1 Command Center?

    Regards

    Stuart

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Stuart,
    When you changed the termination character to @, did you put one after the END statement (... END @)? DB2 is very picky when doing
    compound statements.

    Andy

    Originally posted by svcook
    Andy,

    I changed the termination charcter to @, but I was still getting the same error unexpected token error message. I tried the trigger termination in a number of ways with ; and @ and without any termination character. Could it possible be a bug with the 7.1 Command Center?

    Regards

    Stuart

  9. #9
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9
    Andy,

    Yes I tried that, and it didn't seem to make any difference. See below...

    CREATE TRIGGER REPORTER.REP_AUDIT_INSERT
    AFTER INSERT ON REPORTER.REPORTER_STATUS
    REFERENCING NEW AS n
    FOR EACH ROW
    MODE DB2SQL
    WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-17.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ;
    END@


    ------------------------------ Command Entered ------------------------------
    CREATE TRIGGER REPORTER.REP_AUDIT_INSERT
    AFTER INSERT ON REPORTER.REPORTER_STATUS
    REFERENCING NEW AS n
    FOR EACH ROW
    MODE DB2SQL
    WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-17.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ;
    -----------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token ")" was found following "0, 0, 'NCOMS', 1234".

    Expected tokens may include: ")". SQLSTATE=42601


    Regards,

    Stuart

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Stuart,
    It has me puzzled. Maybe you need to open up a PMR with IBM
    on this one.

    Andy

    Originally posted by svcook
    Andy,

    Yes I tried that, and it didn't seem to make any difference. See below...

    CREATE TRIGGER REPORTER.REP_AUDIT_INSERT
    AFTER INSERT ON REPORTER.REPORTER_STATUS
    REFERENCING NEW AS n
    FOR EACH ROW
    MODE DB2SQL
    WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-17.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ;
    END@


    ------------------------------ Command Entered ------------------------------
    CREATE TRIGGER REPORTER.REP_AUDIT_INSERT
    AFTER INSERT ON REPORTER.REPORTER_STATUS
    REFERENCING NEW AS n
    FOR EACH ROW
    MODE DB2SQL
    WHEN ( n.lastmodified is not null )
    BEGIN ATOMIC
    INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-17.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ;
    -----------------------------------------------------------------------------
    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token ")" was found following "0, 0, 'NCOMS', 1234".

    Expected tokens may include: ")". SQLSTATE=42601


    Regards,

    Stuart

  11. #11
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9
    Andy,

    I was thinking the same. It's moaning about something which we know works. Thanks for you hel on this. I'll log a ticket. Do you know if 7.1 is still an active version?

    Regards,

    Stuart

  12. #12
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9
    Andy,

    Sorry... hel was supposed to spell help.. I think it was a Freudian slip

    Regards,

    Stuart

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Stuart,
    as far as I know 7.1 is still vaild for support.

    Andy

    Originally posted by svcook
    Andy,

    I was thinking the same. It's moaning about something which we know works. Thanks for you hel on this. I'll log a ticket. Do you know if 7.1 is still an active version?

    Regards,

    Stuart

  14. #14
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    V.1 support is no more available...

    You need to be on 7.2 atleast

    Cheers
    Sathyaram

    Originally posted by ARWinner
    Stuart,
    as far as I know 7.1 is still vaild for support.

    Andy
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  15. #15
    Join Date
    Aug 2003
    Location
    London, UK
    Posts
    9
    I am still having problems with the trigger creation, but this time it's in respect to having a IF statement within the trigger body. I am using thr command line processer to create this trigger which has the following body:

    CREATE TRIGGER REPORTER.TEST AFTER INSERT ON REPORTER.REPORTER_STATUS REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN ( n.lastmodified is not null ) BEGIN ATOMIC IF ( '1' IS NOT NULL ) THEN INSERT INTO REPORTER.REP_AUDIT_ACK VALUES ( 1234, '2003-04-08-17.19.00', '2003-04-08-18.19.00', 0, 0, 'NCOMS', 1234 ) ; END IF; END @

    and the error being returned is

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "IF" was found following "null ) BEGIN ATOMIC". Expected tokens may include: "SELECT". SQLSTATE=42601

    A trigger without an IF statement creates without error.

    Any thoughts on this anyone?

    Thanks in advance,

    Stuart

Posting Permissions

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