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
