Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 20 of 24 total |
After Update Trigger still has record locked |
Wed, Aug 8 2012 6:25 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
No, but it will try - it wont because of the records becoming locked as they are updated, and the cascade stops. That’s what I was talking about in my first post when I said Barry would have to be careful about the possibility of recursive calls. The original problem, that is the records remaining locked, is what prevents this second problem BTW, Barry you have a WHEN condition that was supposed to avoid it (recursive calls) by only firing the trigger when one specific column is changed, but the condition doesn't look right to me as it will fail with INSERT operations because it references OLDROW and that’s not allowed. -- Fernando Dias [Team Elevate] |
Wed, Aug 8 2012 8:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
>No, but it will try - it wont because of the records becoming locked as >they are updated, and the cascade stops. I'll take your word for it but it doesn't make sense to me and I can't think of an easy way to test it My difficulty is that what you seem to be suggesting is that if the trigger hits a lock the exception is silently eaten by ElevateDB and it continues. If that's not how it happens nothing will ever progress past the row that initiated the cascade. If that is how it happens then I really don't like it. Roy Lambert |
Wed, Aug 8 2012 8:08 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
<< My difficulty is that what you seem to be suggesting is that if the trigger hits a lock the exception is silently eaten by ElevateDB and it continues.>> No, no the exception is raised, that’s how it stops the recursive calls. -- Fernando Dias [Team Elevate] |
Wed, Aug 8 2012 8:37 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
I'm thinking I haven't been clear. The recursive call of trigger CAN happen, and it would if Barry didn't have that WHEN condition; however, that condition isn't valid because it can't be used with Insert operations because it references OLDROW. So, assuming he would delete the WHEN condition, it would really cause recursive trigger calls with Update operations. But, because the rows are being locked, the recursive call would then stop because an exception would be raised because of the locks. So, the solution would be to use OLDROW as you said before to modify the row being updated, but for that to work he is going to break the processing in more than one trigger, BEFORE triggers for inserts and updates and AFTER triggers for deletes, because he cant use set the OLDROW value in AFTER triggers. Also, he would have to make the BEFORE UPDATE trigger to fire only for "Placement_Date" changes and not for other column changes by using "BEFORE UPDATE OF "Placement_Date" ON " instead of a when condition . The locks are not an issue for deletes or inserts, the only issue is with updates, because the row being updated stays locked, and therefore we cant update it in the AFTER triggers. Also the recursive calls aren't a problem for deletes or inserts, only updates because for every update the triggers are going to make MORE updates on the same table firing the triggers again. -- Fernando Dias [Team Elevate] |
Wed, Aug 8 2012 8:45 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
>No, no the exception is raised, that’s how it stops the recursive calls. In that case could Barry just add exception handling to his SP? Roy Lambert [Team Elevate] |
Wed, Aug 8 2012 10:16 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
<<> In that case could Barry just add exception handling to his SP?>> No, how would that unlock the locked row? See my previous post. -- Fernando Dias [Team Elevate] |
Wed, Aug 8 2012 10:55 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
><<> In that case could Barry just add exception handling to his SP?>> > >No, how would that unlock the locked row? It wouldn't - he'd still have to handle that row in the trigger but if you just eat the exception it should process the other rows? Roy Lambert [Team Elevate] |
Wed, Aug 8 2012 5:20 PM | Permanent Link |
Fernando Dias Team Elevate | Roy,
Isn't it simpler and safer to just avoid it? -- Fernando Dias [Team Elevate] |
Thu, Aug 9 2012 7:31 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
>Isn't it simpler and safer to just avoid it? Generally yes but sometimes no <vbg> Roy Lambert [Team Elevate] |
Thu, Aug 9 2012 8:02 AM | Permanent Link |
John Hay | Barry,
Notwithstanding the particular issues with the locking in triggers, I suspect this sort of operation is not an ideal candidate for a trigger. It will potentially slow down insert/update/deletes if/when the table gets large and is unlikely to work well if there is more than one user. Depending on what Placement_Num is used for it might be better to have it being dynamically calculated for display/reporting. John |
« Previous Page | Page 2 of 3 | Next Page » |
Jump to Page: 1 2 3 |
This web page was last updated on Thursday, September 26, 2024 at 10:04 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |