Re: SQL BEFORE puzzle
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Tue, 05 Aug 2008 11:28:22 -0300
Message-ID: <48986389$0$4012$9a566e8b_at_news.aliant.net>
>
> I think it's largely for practical reasons.
> An AFTER trigger operates on a row that has already been
> physically placed somewhere in the table/index. If the trigger
> wants to alter a key value, the row probably has to move,
> and you end up with physical storage issues. BEFORE
> triggers avoid this, so they are preferred if part of the
> action is to calculate or update primary or secondary
> key columns.
>
> Of course this is all implementation dependent, but
> I suspect most implementations would work that way.
Date: Tue, 05 Aug 2008 11:28:22 -0300
Message-ID: <48986389$0$4012$9a566e8b_at_news.aliant.net>
kschendel wrote:
> On Aug 4, 4:32 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
>>I would expect both the BEFORE and AFTER triggers to have a view of both >>the before and after images of the data. I don't really see the need for >>before and after triggers. Perhaps I am just missing it.
>
> I think it's largely for practical reasons.
> An AFTER trigger operates on a row that has already been
> physically placed somewhere in the table/index. If the trigger
> wants to alter a key value, the row probably has to move,
> and you end up with physical storage issues. BEFORE
> triggers avoid this, so they are preferred if part of the
> action is to calculate or update primary or secondary
> key columns.
>
> Of course this is all implementation dependent, but
> I suspect most implementations would work that way.
Given that a rollback could undo the work, I would expect only the log to reflect updates in any case so I am not sure what the practical benefit would be even with the above considerations. Received on Tue Aug 05 2008 - 16:28:22 CEST