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 - 1140 days ago
Last viewed on: 24 Sep 2017 (2216 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: 3 years 1 months 17 days 1 hours 2 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: 3 years 1 months 15 days 4 hours 31 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: 3 years 1 months 14 days 2 hours 15 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.