Re: Trigger Failing

H_tompson_at_hotmail.com (Harry) wrote in message news:<413386d4.0203280802.8bd1825_at_posting.google.com>...
> Hi all,
>
> We have a trigger that updates table B when table A is modified.
>
> If it can't update table B immediatly (because of a lock on the
> table for example) what will it do? Will it fail? Will it wait?
>
> Is there anyway to control this behavior?
>
> Thanking you..
> Harry

Harry, the default behavior will be for the updater to wait for the locking session to commit or rollback and then perform its operation.

Your options for changing this default behavior depend on your version of Oracle and on your requirement for the update to B to have to take place verse would like it to take place, but it doesn't have to.

One option is to code a select for update with the nowait option and capture the error if the target row is locked and then issue an error to the table A updater to try later.

The cooperative FAQ article How do you identify all locked rows in a table ?
at http://www.jlcomp.demon.co.uk/faq/locked_rows.html constains a sample of doing this. With version 9 you would have the option of specifing how long you wait select .... for update wait n.

Per a Mark J Boback post on metalink:
Where n is time to wait, in seconds.

Yup, I didn't believe it when I first read it either. But I just tried it, and it's true.

If you specify NOWAIT, it still raises an ORA-0054 resource busy and acquire with NOWAIT specifed.

If you specify WAIT n, and the time expires, it raises an ORA-30006 resource busy; acquire with WAIT timeout expired.

-Mark

• Mark D Powell --