Hi, The other option that you have is to use Advance Queuing. You can insert the row and a message on a queue within the same transaction. Your procedure will be a consumer of the messages in the queue.
If the transaction that does the insert is rolled back then the message is never placed on the queue and your procedure is never executed. The dequeue of the message is also transactional so if your procedure fails the message will be left on the queue and redelivered. You need to handle the redelivery case is a sensible manner - that is, you do not want the message to be redelivered continually if it is going to fail all the time. Cheers, Craig. -----Original Message----- Sent: Friday, 29 November 2002 5:04 AM To: Multiple recipients of list ORACLE-L Andrey Bronfin wrote: > Dear gurus! > I'm looking for a solution to the following problem: > I need a way to run a certain stored procedure as soon as a record is > inserted into a certain table. > A trigger is not feasible for this, since I do not want the execution of the > procedure to be a part of the transaction that inserts a row into the table. > I want the insertion to be visible to all the users (i.e. committed) as soon > as the insertion is done, and then, as a separate transaction of its own, to > run the stored procedure. > Suggestions , please ? > Thanks a lot ! Keyword = AUTONOMOUS TRANSACTION -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Craig Munday INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
