I am migrating a trigger from Informix to DB2 and am having a problem with the syntax. This is an update trigger that checks to see if a "similar" row exists in the table and raises and application error if so.

I converted Informix' WHEN statement to an IF statement which led to syntax errors. Looking at the examples in the manual I came to the conclusion that the trigger is expecting a value to be created at the end of the body (to set the SQLSTATE?) so I rearranged the IF statement as a CASE statement inside of a VALUES statement. This lead to an error that the EXISTS clause is not supported in the WHEN clause.

I discovered that UDFs seem to have similar syntax restrictions so I created a stored procedure. That compiles Ok but I can't get to it from the trigger (no CALL statement in compound dynamic SQL). Now what?

I am having quite a time trying to figure out what is and isn't allowed in a trigger and function body. The SQL manual says that an IF statement is allowed but there appear to be restrictions on what is allowed in it, and where it is allowed in the body. Is there a better description of what exactly is expected here?

I am running DB2 UDB 8.1.2 FP3 on RedHat ES 2.1.

Thanks.

Ok, I managed to solve this one myself. It turns out that a lot of my problems are related to the fact that the parser doesn't seem to like blank lines. I don't know what is so special about "JOIN <joined_table>" but that seems to be its way of saying "error in above program". The source program elements mentioned in the error seem to have no correlation to the actual problem. Commenting out the blanks made it easier to interpret the results.

There are still a couple of issues with testing - the fact that raise_error returns null seems to cause problems in certain cases - SIGNAL SQLSTATE does fine provided that you can hardcode the SQLSTATE in the statement (that is, it is not provided in a variable).

Informix' multiple WHEN syntax can either be converted to IF statements, or failing that can be broken up into multiple triggers. DB2 seems to be able to handle whatever Informix can in the WHEN clause in the trigger definition, including the EXISTS clause I was having problems with.