Home » SQL & PL/SQL » SQL & PL/SQL » Why Commit is not allowed in a trigger in oracle??
Why Commit is not allowed in a trigger in oracle?? [message #606225] Tue, 21 January 2014 05:08 Go to next message
sampathkore4444@gmail.com
Messages: 8
Registered: January 2014
Junior Member
Please explain me the reason for not allowing commit inside a a normal trigger (not autonomous trigger) in a clear cut manner.......
Re: Why Commit is not allowed in a trigger in oracle?? [message #606226 is a reply to message #606225] Tue, 21 January 2014 05:30 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Because you would lose all hope of maintaining data integrity if you commit inside a trigger.

See this Ask Tom thread for example.
Re: Why Commit is not allowed in a trigger in oracle?? [message #606230 is a reply to message #606226] Tue, 21 January 2014 05:35 Go to previous messageGo to next message
sampathkore4444@gmail.com
Messages: 8
Registered: January 2014
Junior Member
Thanks for the reply Thomas......But still i was not clear why commit is not allowed inside a trigger
Re: Why Commit is not allowed in a trigger in oracle?? [message #606232 is a reply to message #606230] Tue, 21 January 2014 05:41 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Did you read the AskTom article that Thomas provided the link and in particular do you understand the concept of an atomic operation?
Re: Why Commit is not allowed in a trigger in oracle?? [message #606233 is a reply to message #606230] Tue, 21 January 2014 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You read and study this AskTom thread in 5 minutes! Wow!
Now explain what is not clear for you and in Thomas' answer.

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 Go to previous message
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
Previous Topic: Passing bind variables to a VIEW
Next Topic: Number of Primary Key Columns
Goto Forum:
  


Current Time: Thu Mar 28 04:25:30 CDT 2024