Midrange News for the IBM i Community


Posted by: renojim
Still can't rollback an sql trigger
has no ratings.
Published: 08 Aug 2014
Revised: 12 Aug 2014 - 1074 days ago
Last viewed on: 20 Jul 2017 (2077 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Still can't rollback an sql trigger Published by: renojim on 08 Aug 2014 view comments(3)

Well, I've been trying for two days to trap an error and rollback in an sql trigger. Anybody know how?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Still can't rollback an sql trigger
Posted: 2 years 11 months 13 days 5 hours 7 minutes ago

You said it was pure SQL you were using for the trigger?  Then I would check the SQL State.

 

Posted by: renojim
Premium member *
Comment on: Still can't rollback an sql trigger
Posted: 2 years 11 months 11 days 8 hours 36 minutes ago

Yeah, that's not the problem, I can get the error from code or state, but I can't do anything with it. I finally found that rollback and commit aren't allowed, but you can create a 'savepoint' and 'rollback to savepoint'. Problem now is, as soon as I get the error, like 'duplicate key', the process drops straight to the final 'end' statement at the end of the program. Skips over everything else, including any if statement where I could do something - rollback to savepoint - and I can't figure out any way around it.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Still can't rollback an sql trigger
Posted: 2 years 11 months 10 days 6 hours 20 minutes ago

There used to be a WHENEVER clause that was used for SQL exception/error handling. Perhaps it is still supported in SQL-based triggers.