Date: Sun, 17 Feb 2008 22:38:26 -0500
Message-ID: <>

DA Morgan wrote:

> DJH wrote:

>> DA Morgan wrote:
>>> DJH wrote:
>>>> I am writing a trigger to calculate the value of a column in a row
>>>> in a
>>>> another table in processing the current trigger for a different table.
>>>> I need to make sure that the row in the other table is not updated by
>>>> other processes while I am processing this trigger.
>>>> How may I do that?
>>>> Also can you run a "select count(*) where .." on the current table you
>>>> are processing in the trigger without getting "mutating.. type errors."
>>>> I am using 10G.
>>>> I need to check the inventory of the item before I can let the order
>>>> through but also I need to check current order table to see if there
>>>> are
>>>> any unprocessed orders that pre-reserved the same items from the
>>>> inventory.
>>>> So total available items in inventory will be Inventory Items -
>>>> Unprocessed Order PreReserved Items.
>>>> If available items are not there to process current order I have to
>>>> prevent it from going through. Meanwhile I cannot let anyone update
>>>> the Inventory until I finish inserting the order with its pre-reserved
>>>> items.
>>>> Thanks
>>> SELECT *
>>> FROM second_table
>>> WHERE some_condition
>>> Read the docs before you do this so you fully understand what is
>>> happening.
>> Thanks DA but I get his error:
>> I am using a select count() statement with 'for update' in a trigger.
>> ORA-01786: FOR UPDATE of this query expression is not allowed
> What query? You've not posted a query.
> Provide full version info. too.

Re: Oracle Database 10g Release - 64bit Production

DA I could not do a

select sum(inv_total) into localvariable from inventory where product_id = 'something' for update; -- results in compile error

I could do:

select inv_total into localvariable from inventory where product_id = 'something' for update; -- this will lock those records but cannot get sum of inv_total for the product.

But the above will also throw an exception since it will return many records for some products with multiple inventory records per product - some being reserve inventories etc.

I would prefer to do the sum() operation to get the total from all inventory records for a product and perform a lock operation so that I can check and decrement while the record/row is locked.

All of this in the trigger for the order being submitted.

Again I want to get a sum and lock at the same time. Received on Sun Feb 17 2008 - 21:38:26 CST

Original text of this message