关于Trigger的使用 - 转自AskTom

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