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
