|
|
|
|
|
Re: Why Commit is not allowed in a trigger in oracle?? [message #606276 is a reply to message #606233] |
Tue, 21 January 2014 22:56 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
What is needed is a basic understanding of transaction semantics. This is very very basic RELATIONAL DATABASE rules. You need to do some reading about transactions and how they work, NOT FROM AN ORACLE PERSPECTIVE, but from a simple relational database understanding. Once you understand what a transaction is and how the rules of relational database management avoid UPDATE ANOMALIES when using data, then you can answer for yourself the reason why a commit inside a trigger would destroy all hope of data integrity.
To help you along, consider what it would mean if a commit was done and then there was some failure after the commit was issued. A rollback would be necessary. But what would get rolled back?
Once you have this under your belt, then you can have a go at Oracle's hybrid concurrency/consistency transaction model and its details related to triggers. You can consider things like "what is a consistent set of data for a SQL statement", and "hidden rollback inside triggers", and other such details.
It is all very complicated. Triggers are not extensions of application logic. Your application should work whether the tables it is changing have triggers of not. A DBA should be able to add or drop triggers on the tables used by your application at any time and your app logic should not care one way of the other. If you app needs triggers to work correctly then you have abused triggers and will pay for it later.
The most common VALID uses for triggers are:
1. for complex data edits that the database enforces. These are database checks independent of any single application because all applications will be subject to the same complex edits.
2. for user defined auditing. This is where autonomous transactions come into play.
3. for user defined replication schemes.
What are you using triggers for?
Will your application work without the triggers?
The most common abuses of triggers are:
1. to avoid TABLE IS MUTATING OR CONSTRAINING error.
2. to update the database inside a query (usually in order to monitor accessed rows or columns).
3. to avoid editing applications during steady state phase of an application's life (e.g. maintenance) by pushing application specific logic into the database as a short-cut way to avoid updating possibly many apps at once with the same change.
Good luck. Kevin
|
|
|