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 - 1201 days ago
Last viewed on: 24 Nov 2017 (2340 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 3 months 17 days 16 hours 25 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 3 months 15 days 19 hours 54 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 3 months 14 days 17 hours 38 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.