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

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.

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.