Home » SQL & PL/SQL » SQL & PL/SQL » How to wait for trigger to complete (merged)
How to wait for trigger to complete (merged) [message #395185] Tue, 31 March 2009 10:13 Go to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Hi all,

I have two schema:
SHEMA A
SCHEMA B

From SHEMA A , I execute a stored procedure that inserts data in a table of SCHEMA B.
On that table there's a trigger that fires AFTER INSERT and modify data in the same table.

The problem is that the procedure in SCHEMA A, after inserting data, makes a select on SCHEMA B table and the execution returns an error saying that the table is beeing modified by the trigger.

Is it possible to solve the problem inserting a wait command, like dbms_lock.sleep, in SCHEMA A procedure after the insert statement?
If I could wait for 5 seconds I think that the trigger would be completed and the procedure would be able to complete the select...

Many thanks to all will help!
Best regards.
Re: How to wait for trigger to complete? [message #395188 is a reply to message #395185] Tue, 31 March 2009 10:27 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Could you post the code you are using and the error message?

As far as I'm aware selects can't error out because of triggers ever.
Re: How to wait for trigger to complete (merged) [message #395190 is a reply to message #395185] Tue, 31 March 2009 10:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>makes a select on SCHEMA B table and the execution returns an error
ERROR? What Error? I don't see any error.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: How to wait for trigger to complete? [message #395368 is a reply to message #395188] Wed, 01 April 2009 04:16 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
That might be MUTATING TRIGGER error, according to me.

regards,
Delna
Re: How to wait for trigger to complete (merged) [message #395370 is a reply to message #395185] Wed, 01 April 2009 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the select statement is being run from within a trigger it could give mutating table, but OP implies that it isn't being run from a trigger.
Re: How to wait for trigger to complete (merged) [message #395392 is a reply to message #395370] Wed, 01 April 2009 05:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
From SHEMA A , I execute a stored procedure that inserts data in a table of SCHEMA B.
On that table there's a trigger that fires AFTER INSERT and modify data in the same table.


I think the OP is misreading his error messages.

I read the above as 'I've got an After Insert trigger on B.TABLE that selects data from B.TABLE'

I think it's the INSERT that's erroring, not the select, and that it's a mutating Table error as @Delna suggests.
Re: How to wait for trigger to complete (merged) [message #395639 is a reply to message #395185] Thu, 02 April 2009 02:28 Go to previous messageGo to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Thank you all for your support.

As discussed, the error was a "mutating table" and I solved it throug a workaround.

Since the operations done by the trigger are not necessaries for the procedure, I put at the beginning of the procedure a command line to disable the trigger and then a second one to re-enable it at the end of the code.

Thanks once more and have a nice day!
Re: How to wait for trigger to complete (merged) [message #395683 is a reply to message #395185] Thu, 02 April 2009 04:31 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I really hope your system is single user system or you've almost certainly introduced a bug.
What happens when someone does something that requires the trigger to fire while it's disabled?
Previous Topic: Error invalid directory path in oracle 10.2
Next Topic: ORA-22285: non-existent directory or file for FILEOPEN operation
Goto Forum:
  


Current Time: Thu Dec 08 10:26:08 CST 2016

Total time taken to generate the page: 0.14474 seconds