Re: How to check SQLCODE in trigger

Hmm, OK perhaps I am a bit slow these days, but I think I start to get 
what you are doing... and it won't work...

When you insert into a DATE column. DB2 will aggressively(!) ensure the 
date is sane. That is this error is being raised before your trigger is 
being called.
Now clearly DB2 will not allow bad dates into the table (which is what 
you rely on with your AFTER trigger.
But DB2 will also not allow bad dates to flow through it's runtime code.
Thus even a BEFORE trigger will do you no good.

There are three ways to do what you want to do on the database side:
* Use an instead of trigger on a view where the view maps the DATe in
   the table to a VARCHAR and the INSTEAD OF trigger maps it back
   That is a horrible idea
* Store a string in the database instead of a date.
   Preferably in a yyyymmdd format, so you can do comparisons on it.
   That is slightly less horrid
* Use a stored procedure instead of an INSERT to drive your
   modification.
   There are users who do this on principle.
   "No SQL other than a CALL in my app"
   This way your proc can do everything it wants to and you
   insert once you are satisfied.

Now, all this can be avoided if you ****ft your thinking:
DB2 gave you a perfectly good error message saying exactly what you 
wanted to say. Why not use it? Let the application catch the -181 (or 
the associated SQLSTATE which is likely ANSI Standard)

Cheers
Serge
-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab