Good detective work, Bob! David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Bob Taylor" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Wednesday, February 02, 2005 7:04 PM Subject: [RBG7-L] - Re: After Update Trigger - Resources required by command are not available.
I have discovered that the problem only occurs in multi-user mode (set multi on). In single user mode, the after update trigger can successfully update the same record. I am going to report this as a bug since inserts are not subject to the same restriction in multi-user mode. Thanks to all the responded. Bob On Wed, 2 Feb 2005 16:51:27 -0600, Bob Taylor wrote: > The primary update works, but the update I am performing in the > after update trigger does not. > > In particular, the trigger update will not work if I try to update > the same record as the primary update. > > What happens is that when the update statement in the trigger is > hit R:Base freezes for about 10 seconds (I imagine this is the > "Wait Time" setting) and then comes back with the error message > "Resources required by command are not available." > > If I change the after update trigger such that it is no longer > attempting to manipulate the same record as the primary update then > all works as expected. > > The key seems to be updating the same record in the trigger that > the primary update is against. > > Oddly enough, everything works as expected for after inserts > triggers so that I CAN update the same record that was just > inserted. > > I have a stored procedure like this... > > update table1 + > set col2 = 'Hello' + > where col1 = 100 > > return > > I then add trigger.... > > alter table table1 add trigger update after KacCvalOptTAU > > assuming I have a single record in the table with a col1 value of > 200, if I run this command > > update table1 set col2 = 'Bob' where col1 = 200 > > I will get a "no data found" from the trigger (as expected) and > then the primary update succeeds. > > However, if I run this.... > > update table1 set col2 = 'Bob' where col1 = 100 > > then I get the noted error message. > > Keep in mind that this is a dumbed-down version of my original code > that uses a variable to prevent an endless update loop. > > That version of my code suffers from the same problem as this > simplistic version. > > Thanks in advance. > > Bob > > On Wed, 2 Feb 2005 08:14:39 -0500, David M. Blocker wrote: > >> Bob >> >> 1. Is the update working? >> 2. If not, what exactly is the command that is failing - can you >> post it here for us to look at? >> 3. Can you take the same code and run it from a stand alone file >> or R> prompt and does it work? >> >> David Blocker >> [EMAIL PROTECTED] >> 781-784-1919 >> Fax: 781-784-1860 >> Cell: 339-206-0261 >> ----- Original Message ----- >> From: "Bob Taylor" <[EMAIL PROTECTED]> >> To: "RBG7-L Mailing List" <[email protected]> >> Sent: Tuesday, February 01, 2005 9:38 PM >> Subject: [RBG7-L] - After Update Trigger - Resources required by >> command are not available. >> >> >> I am trying to modify column values in response to >> inserts/updates. >> >> I am attempting to do this with AFTER INSERT and AFTER UPDATE >> table triggers. >> >> The triggers simply get the primary key value using sys_new and >> then execute an update statement back against the table using >> this value. >> >> I have the necessary checks and balances to prevent an endless >> loop of updates. >> >> The trigger works for inserts, and the expected column changes >> take place. >> >> However, for updates I am getting the error "Resources required >> by command are not available" when the update statement within >> the trigger fires. I believe the error code is 2110. >> >> It would seem that if the technique works for inserts that it >> should also work for updates. >> >> Can anyone shed some light on this? >> >> Thanks >> >> Bob
