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.
Well, I've been trying for two days to trap an error and rollback in an sql trigger. Anybody know how?
You said it was pure SQL you were using for the trigger? Then I would check the SQL State.
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.
There used to be a WHENEVER clause that was used for SQL exception/error handling. Perhaps it is still supported in SQL-based triggers.