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.
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?