Re: SQL BEFORE puzzle

From: David BL <davidbl_at_iinet.net.au>
Date: Wed, 6 Aug 2008 19:21:10 -0700 (PDT)
Message-ID: <56aa7d05-ddcc-42c4-91de-a561bde3ae4e_at_2g2000hsn.googlegroups.com>


On Aug 6, 3:16 am, Cimode <cim..._at_hotmail.com> wrote:
> On 5 août, 14:35, kschendel <schen..._at_kbcomputer.com> 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.
>
> 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. Received on Thu Aug 07 2008 - 04:21:10 CEST

Original text of this message