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

    Unanswered: Are multiple statements in one trigger possible?

    I'd like to create an AFTER UPDATE trigger that, when fired, takes the affected rows and inserts them into two separate tables. That requires two insert statements. I could create two separate triggers, each inserting into the desired table. However, I'd much rather consolidate that into ONE trigger, since the data to be inserted is the same for both. However, I'm having trouble with the syntax. BTW, I'm using IBM Data Studio v2.2.1.0 for my IDE. Here is sample code:

    CREATE TRIGGER tgTable1_AfterUpdate
    AFTER UPDATE OF COMPANYNUMBER,
    DIVISIONNUMBER,
    JOBNUMBER
    ON Table1
    REFERENCING NEW AS newrow
    FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    INSERT INTO log1(scono, sdvno, sjbno)
    VALUES(newrow.COMPANYNUMBER,
    newrow.DIVISIONNUMBER,
    newrow.JOBNUMBER);

    INSERT INTO log2(scono, sdvno, sjbno)
    VALUES(newrow.COMPANYNUMBER,
    newrow.DIVISIONNUMBER,
    newrow.JOBNUMBER);
    END;


    When I run this, I get the following errors:

    [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
    [SQL5001] Column qualifier or table NEWROW undefined.
    [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: DECLARE.


    It looks like the IDE is treating the "CREATE TRIGGER.. BEGIN ATOMIC" as one statement, "INSERT INTO ... ); INSERT INTO... );" as a separate statement (thus the second error) and finally, "END;" as a third statement.

    So, it appears that I cannot create a trigger with multiple statements. I'm not sure if this is something with my IDE or if indeed this is a limitation.

    Any help would be appreciated.

    -Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure about IBM Data Studio, but I will guess that the problem is that you need to change the statement terminator of your client tool to "@" and then put a "@" after the last END. That way DB2 can tell the difference between end of statement ( ; ) within the trigger, and end of Trigger ( @ ).

    Some client tools will automatically correct for this, but not most of them.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Marcus,

    Thanks, yes, that was it. After posting this, I went and tried using the iSeries Navigator client to see if I could run it from there. I went to MyConnection->Databases->MySchema and clicked on the "Run SQL Script" option that appears at the bottom (note that MyConnection and MySchema will be dependent on a user's setup). From the SQL script window, I posted my SQL code and it ran without a problem. I then ran some tests and indeed the trigger had posted. So that pointed to the the IBM Data Studio IDE. After some research, I found the following article:

    IBM Integrated Data Management

    I didn't change the default terminator via the preferences. Instead, I went to my SQL script file, right clicked and sure enough, there was a "Set Statement Terminator" option. After changing that to "@" and making the necessary adjustment ("END@"), the script executed without a problem.

    Thanks again.

Tags for this Thread

Posting Permissions

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