Re: Re: a primer on trigger?

On Fri, 4 May 2001, Chris Jones wrote:

> On Fri, May 04, 2001 at 12:59:54PM -0700, Stephan Szabo wrote:
>
> > The problem is that you have the problem in reverse then, unless what's
> > done by the trigger is "optional" sortof. What happens if there is an
> > error, and you can't do the outside operation? You once again end up
> > out of sync with the database, but this time the outside thing is missing
> > the records. You still need something to come in every so often and make
> > sure stuff is correct.
> >
> > This whole class of things is just ugly.
>
> I'm a little weak on database theory, but isn't this what two-phase
> commit is for?

I'm very weak on database theory, but the problem here is trying to
interface to external things. With an after commit trigger, you can know
that the rows are committed safely, but what happens if your external
interface fails. Even if you can rollback the database changes, what
about previous external changes that were made assuming that we were
successful. For example:

transaction adds three rows
transaction commits
post-trigger sends mail 1
post-trigger sends mail 2
post-trigger attempts to send mail 3, but for some reason it fails.
<Now we want to uncommit the rows. But we also want to
unsend the 2 mails to keep everything in the same state>