Home » SQL & PL/SQL » SQL & PL/SQL » trigger with rollback (oracle9i)
trigger with rollback [message #389880] Tue, 03 March 2009 22:13 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Why trigger with commit or rollback statement returning error without pragma autonomous transaction,why trigger with commit or rollback statement not returning error with pragma autonomous transaction please give me the reason?
Re: trigger with rollback [message #389882 is a reply to message #389880] Tue, 03 March 2009 22:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Because having a COMMIT within a trigger could result in unexpected & irreversible changes for an application.
An application programmer may not be aware of a trigger on a table.
The application may require an INSERT INTO TABLEA & the UPDATE TABLEB either occur together or not at all.
If there were a trigger with a COMMIT allowed on TABLEA, the
application would be unable to ROLLBACK after attempting UPDATE TABLEB.
So the reason to not allow COMMIT in normal triggers is to ensure
data integrity remains under the control of application programmers.

[Updated on: Tue, 03 March 2009 22:53]

Report message to a moderator

Re: trigger with rollback [message #389883 is a reply to message #389880] Tue, 03 March 2009 22:56 Go to previous messageGo to next message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
This is a problem caused due to the mutating table error.
When you have a row level trigger on a table that you are updating/inserting/deleting data from, you cannot query the same table ( Unless you give the PRAGMA AUTONOMOUS_TRANSACTION clause..but even in this case you might get undesired results as you do not really get the current data... All you get is the already committed data..).. This is one of Oracle's way to ensure that the results are always consistent.. when you query inside a trigger.

Please check this link for a very clear explanation of mutating tables and why you need PRAGMA AUTONOMOUS_TRANSACTION when querying on the same table.

http://asktom.oracle.com/tkyte/Mutate/



Thanks,
Rajesh.
Re: trigger with rollback [message #389897 is a reply to message #389883] Wed, 04 March 2009 00:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This has nothing to do with mutating tables.
Read BlackSwan's explanation; can't explain it any clearer than that
Re: trigger with rollback [message #389939 is a reply to message #389883] Wed, 04 March 2009 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
rajesh.chamarthi wrote on Wed, 04 March 2009 04:56


Please check this link for a very clear explanation of mutating tables and why you need PRAGMA AUTONOMOUS_TRANSACTION when querying on the same table.

http://asktom.oracle.com/tkyte/Mutate/




Are you suggesting Tom Kyte says you should use PRAGMA AUTONOMOUS_TRANSACTION to get around mutating table errors?!?

Shocked

Cause he hasn't, ever.

Don't use AUTONOMOUS_TRANSACTION to get around mutating table and just don't put it in triggers.
Re: trigger with rollback [message #390472 is a reply to message #389939] Fri, 06 March 2009 08:38 Go to previous message
rajesh.chamarthi
Messages: 11
Registered: November 2006
Junior Member
You are right.... we should not use pragma autonomous_transaction to get around mutating table errors.

I was suggesting that using it does not throw an error becuase it is treated as a completely seperate transaction and the user is therby reading a commited set of data.

I put it the wrong way.





Thanks,
Rajesh.
Previous Topic: how to send data into ref cursor
Next Topic: SELECT statement in procedure
Goto Forum:
  


Current Time: Fri Dec 09 01:49:21 CST 2016

Total time taken to generate the page: 0.15245 seconds