Re: notify clients on database updates

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Fri, 24 Oct 2003 11:02:24 -0500
Message-ID: <vpij89p5bsas29@corp.supernews.com>

A trigger will fire depending on how you set it up. For instance, if you set it up to fire before a row is inserted, it will do exactly that. However, the results of the trigger will not commit unless the transaction causing the trigger to fire commits. If the original transaction rolls back, then the work of the trigger will roll back as well. This assumes that your trigger does DML and is not set up to do autonomous transactions.

HTH,
Brian

On Friday 24 October 2003 10:54, roger thoughtfully contributed:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in news:1066971957.112378
> @yasure:
>

>> That was intended as two independent suggestions. Sorry if it looked
>> like step 1, step 2.
>> 

>
> Right. Got it.
>
> My remaining question is that of whether it is possible to
> get a trigger to fire only when the transaction is committed.
> (see below)
>
> If not, then I'll probably have to go the way of writing to
> a notification table (from the update trigger, using the same
> transaction context) and then polling the table (separate process)
> to look for new rows that will show up as transactions are comitted.
>
> Any thoughts on that part?
>
> I'm not wild about the polling approach, so if there's
> any other options...
>
>
> Thanks again.
>

>>>
>>>Either way - one wrinkle I forsee is that the pipe
>>>reader will potentially be notified before the
>>>change to the table is comitted, or perhaps even rolled back.
>>>
>>>Is there any mechanism to cause a trigger like this
>>>to fire only after the transaction is comitted?
>>>
>>>I don't see it off hand.
>>>
>>>Thanks.
>>>
>>>  
>>>

Received on Fri Oct 24 2003 - 11:02:24 CDT