|
|
|
|
| triggers [message #278459 is a reply to message #278457] |
Sun, 04 November 2007 08:58   |
sunilcse1
Messages: 23
Registered: June 2007
Location: Bangalore
|
Junior Member |
|
|
|
Can we write a trigger that will fire when there is a change in column value?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: commit in a trigger [message #278488 is a reply to message #278468] |
Sun, 04 November 2007 13:25 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
It may not be obvious where to look in the documentation. CREATE TRIGGER (SQL Reference) says "The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction", which may leave the OP wondering how the bit about transactions works. The PL/SQL Reference doesn't have anything obvious about commits in the small section on Database Triggers either.
For situations such as logging or auditing, you might want to look at Autonomous Transactions (more details and examples here) which allow you to commit or rollback independently of the calling process.
If the requirement is not to do with logging/auditing (i.e. inserting rows in a table regardless of whether the main process succeeds or fails) then autonomous transactions may not be the right approach, hence Michel's "sometimes". Why do you need to commit in a trigger?
|
|
|
|
| Re: commit in a trigger [message #278490 is a reply to message #278488] |
Sun, 04 November 2007 13:41 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Given the bunch and level of questions OP posted at the same time, I'm not really sure he is able to understand the answer.
About your question "Why do you need to commit in a trigger?" I'm pretty sure he didn't need anything but answer to some homework or interview questions.
Once again, given the bunch of questions, it is better to point him to the whole documentation and start to really answer when he'll have the basics.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
