-- Thanks for the question regarding "Trigger on a table Fire at commit.", version 9.2.0
Submitted on 18-Jul-2007 12:40 Central time zoneTom's latest followup | Bookmark | Bottom
Last updated 17-Sep-2009 18:20
You Asked
Hi,
I have few transactional tables.
Tab_M is the Master transactional table.
All other concern tables are also transactional and child(or child-to-Child)
of the table Tab_M.
I want to put a Trigger to hold some bussiness logic.
In a regular scenario, record inserted on Tab_M then all other
transactional tables and Finally commit takes place.
I want this trigger will fire only when a transaction is Commited.
Thanks
and we said...
there is no "on commit" trigger.
I hate triggers, I especially hate the concept of this one - likely you are going to try and enforce some entity integrity constraints - but you cannot do that in triggers without LOCKING tables in general (and I doubt you would do that)
My recommendation:
create a stored procedure that does the transaction from start to finish, performing whatever checks you find necessary. DO NOT mess around with triggers, DO NOT implement logic in triggers. Use a transactional API in the form of stored procedures - do not allow applications to directly modify the tables - have them call these well formed, correct procedures.