Re: How to check SQLCODE in trigger

Thank you Serge for  promt responce.
But i am using this trigger for screen edit.
When edit is passed (sqlcode = 0 after insert) row is deleted from the
table.
But if i have  several DATES on the screen -180 OR -181 does not tell me
what
date field is wrong.
Message generated by trigger: '292 INVALID HIREDATE' tells me what 
field on the screen is invalid and 292 is  the HIREDATE screen position
which
will be used in SEND MAP CICS statement ,to position cusrsor in invalid
field.

Using triggers for edit saved us 70% coding time and also made available
all best DB2 features insted 1000's  lines of COBOL coding.    
So if i can not use triggers to overlay system generated error message,
how can I resolve this issue. May be Constraint on data fields will help?
Thahk You again Serge. I learn a lot new things on this board.       


Date is invalid.    

Serge Rielau wrote:
>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

-- 
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1