Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
Update trigger |
Sat, Aug 9 2008 1:27 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Aug 11 2008 12:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Aug 11 2008 1:03 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Aug 11 2008 1:32 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Aug 12 2008 3:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Aug 12 2008 10:20 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Aug 12 2008 11:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Aug 12 2008 5:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Aug 13 2008 2:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Monday, July 15, 2024 at 07:03 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |