How do I do the following:

Change a field in tableA and alter tableB where the foreign key matches tableA id.

I can easily write the sql to do the update but what I need help with is what to do if one of the records in tableB is locked for editing.

Roy Lambert

Roy,

<< I can easily write the sql to do the update but what I need help with is
what to do if one of the records in tableB is locked for editing. >>

You can enclose it in a BEGIN..EXCEPTION block that will trap the row lock
error and either keep trying or let the exception bubble up.  Just make sure
to wrap the update into a transaction to ensure that it's a 100%
all-or-nothing proposal.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I can figure out how to do that using table events and triggers but not using triggers only. No rush but I'd appreciate either a pointer to the right bit of help or a simple example.

Roy Lambert

Roy,

<< I can figure out how to do that using table events and triggers but not
using triggers only. No rush but I'd appreciate either a pointer to the
right bit of help or a simple example. >>

Actually, scratch that.  The best way to handle this is outside of a trigger
inside of one transaction, but if you insist on trying to duplicate
cascading updates, then you'll need to use the Before, After, and Error
Update triggers like this:

Before - Start transaction on current table and FK table only
After - Execute update on FK table with NEWROW values from update and commit
transaction
Error - handle any error due to a row lock error in the After trigger by
rolling back the transaction and then re-raising the exception

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Actually, scratch that. The best way to handle this is outside of a trigger
>inside of one transaction, but if you insist on trying to duplicate
>cascading updates, then you'll need to use the Before, After, and Error
>Update triggers like this:

Have I missed something - duplicate? I know its in the plan but I didn't think you'd built cascading updates in yet.

Roy Lambert

Roy,

<< Have I missed something - duplicate? I know its in the plan but I didn't
think you'd built cascading updates in yet. >>

No, they're not in there yet.  My point was that you're trying to duplicate
the functionality provided by cascading updates.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>No, they're not in there yet. My point was that you're trying to duplicate
>the functionality provided by cascading updates.

This was what prompted the email about the manuals. Glad I haven't missed them (especially with the turn off RI switch). Which train are they on? ie do I wait or roll my own?

Roy Lambert

Roy,

<< This was what prompted the email about the manuals. Glad I haven't missed
them (especially with the turn off RI switch). Which train are they on? ie
do I wait or roll my own? >>

The release notes for every release contains any new features along with
links to the proper places in the manual.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>The release notes for every release contains any new features along with
>links to the proper places in the manual.

Its a thought - I'll look into printing out from the web based manual.

Roy Lambert