Midrange News for the IBM i Community


Posted by: renojim
COMMIT in sql trigger
has no ratings.
Published: 07 Aug 2014
Revised: 07 Aug 2014 - 1024 days ago
Last viewed on: 27 May 2017 (1777 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.

COMMIT in sql trigger Published by: renojim on 07 Aug 2014 view comments(1)

So here's todays quandary. I have to write a trigger using SQL procedure syntax (CREATE TRIGGER) that has three consecutive INSERTS. I would then like to COMMIT those three inserts but IBM won't let me:  "COMMIT is not allowed in a trigger if the trigger program and the triggering program run under the same commitment definition". I do not see a way to run the trigger under a different commitment definition either, so I don't know why they have that stipulation in their statement. To test the trigger, I add a fourth insert that will fail with a duplicate key error. So, I run a program that inserts a record to the file that runs the trigger. Immediately after the insert, I have a COMMIT. Commitment control is on, all the files are journaled. On the insert, the trigger runs, the first three inserts work, the fourth insert fails, the trigger ends, the records are committed. But I don't want them to be. I want a rollback when the fourth insert fails. So what do I do?

 

 

 

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

COMMENTS

(Sign in to Post a Comment)
Posted by: renojim
Premium member *
Comment on: COMMIT in sql trigger
Posted: 2 years 9 months 21 days 5 hours ago

Nevermind, got it, trap the error and execute rollback manually. Thought I remembered it a bit more automated.