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

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
FOR UPDATE;

Read the docs before you do this so you fully understand what is happening.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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
> FOR UPDATE;
>
> Read the docs before you do this so you fully understand what is happening.

Thanks DA but I get this error:

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
> FOR UPDATE;
>
> 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

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
>> FOR UPDATE;
>>
>> 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
Do you just use the COUNT() because you don't want the values?
You could pump the values into an array instead (BULK COLLECT)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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
>> FOR UPDATE;
>>
>> 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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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
>>> FOR UPDATE;
>>>
>>> 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 10.2.0.2.0 - 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.

On Feb 17, 10:38*pm, DJH <NOS...@NOSPAM.COM> wrote:
> 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
> >>> FOR UPDATE;
>
> >>> 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 10.2.0.2.0 - 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

do the sum yourself in a loop. Sounds like you would have 2 inventory
rows (regular stock and reserved?) so the loop will be fast.

This seems like an odd table design, maintaining inventory in two
places for the same stock.

Ed

DJH wrote:
> 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
>>>> FOR UPDATE;
>>>>
>>>> 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 10.2.0.2.0 - 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.

Follow Ed's advice but I want to point out to you that you are your
own worst enemy in this.

I asked you to post the query ... you sort of did so but rather than
posting a real query you made something up.

Now you post "throw an exception" and seem to think that someone trying
to help you doesn't need to know what that exception is.

Is there some reason why you are making it hard for people trying to
help you?
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org