commit and rollback on triggers
why we cannote use commit and rollback in triggers?

Software/Hardware used:
oracle 10g standard edition
ASKED: Feb 2, 2012  7:53 AM GMT
UPDATED: February 28, 2012  8:50:05 AM GMT
60,245 pts.

Answer Wiki:
Short answer: It is a restriction imposed by Oracle.

Not so short: If transaction control commands (commit/rollback) were allowed in database triggers, then other programs or stored procedures/functions would not have control over their own transactions. They would not have a way to decide when to commit or rollback their operations.

For example, if a program performs an insert into a database table (which has an after insert trigger with a commit, it that were allowed), and then the program realizes that other required operations cannot be completed, and thus the whole transaction has to be rolled back. What would happen ? The insert has already been committed, so it cannot be rolled back, and thus, the transaction atomicity has been lost.
Last Wiki Answer Submitted:  Feb 2, 2012  3:44 PM (GMT)  by  Carlosdl   60,245 pts.
To see other answers submitted to the Answer Wiki View Answer History.
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _




 

Commit and Roll back is internally used by oracle

 155 pts.