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]

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

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]

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]

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]

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]

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]

Roy,

Isn't it simpler and safer to just avoid it?

--
Fernando Dias
[Team Elevate]

Fernando

>Isn't it simpler and safer to just avoid it?

Generally yes but sometimes no <vbg>

Roy Lambert [Team Elevate]

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