SQL BEFORE puzzle

On 5 août, 14:35, kschendel wrote:
> On Aug 4, 4:32 pm, Bob Badour 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.

The AFTER trigger is a mechanism that can allow non database related
operations to be performed independently from the context of a db
engine. One may for instance add a line in order table and trigger
the transfer of a file to an FTP server. In the absolute they should
not be needed but dbms's support for workflow operations is very poor.

Hope this helps.

On Aug 6, 3:16 am, Cimode wrote:
> On 5 août, 14:35, kschendel wrote:
>
>
>
>
>
> > On Aug 4, 4:32 pm, Bob Badour 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.
>
> The AFTER trigger is a mechanism that can allow non database related
> operations to be performed independently from the context of a db
> engine. One may for instance add a line in order table and trigger
> the transfer of a file to an FTP server. In the absolute they should
> not be needed but dbms's support for workflow operations is very poor.

AFAIK an AFTER trigger is normally called synchronously by the thread
doing the transaction and before it commits or rolls back. There are
two reasons to be wary of interacting with an external system in the
way you describe: Firstly because most generally the transaction can
actually roll back it may create some inconsistency (ie telling a
remote system that something happened when it actually did not).
Secondly, the original transaction will be made to block while these
additional time consuming activities (like FTP) are being performed –
therefore locks won’t be released quickly and concurrency will be
badly affected. Note as well that sending a file using FTP would
normally only need read access to the local DB so doing this within a
mutative transaction (ie from an AFTER trigger) would lose the
benefits of MVCC.

It is generally better to use a separate (and therefore asynchronous
thread) that uses read only transactions to send changes to another
system. Often sequence numbers or time stamps can be used to ensure
it only sends new content. This can often be made resilient to
inconsistency problems if the receiver records where it is up to and
this information is sent to the sender as part of their initial
handshake in the communication protocol. This actually avoids the
need for distributed transactions (ie multiphase commit).

A very useful technique is for an AFTER trigger to signal an
asynchronous thread that is sleeping on a waitable object such as a
semaphore.