Re: notify clients on database updates

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 24 Oct 2003 19:30:59 GMT
Message-ID: <bnbulj$vlvbk$1@ID-82536.news.uni-berlin.de>

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

>> Triggers in Oracle are atomic just like the rest of the code. It does 
>> not work like SQL Server
>> where they are all independent. So ignore whether the trigger fires ... 
>> it is irrelevant. If the
>> code or procedure commits the actions of the trigger are commited. If 
>> they rollback they
>> rollback the trigger's actions too.
>> 

>
> Understood.
>
> aren't part of the transaction context, for example writing a
> message out to a pipe.
>
> If I do it that way, then the problem is that a message reader
> could see the messages regarding transactions that haven't
> happened yet (not committed) or could see messages about changes
> that never take place (because the transaction gets rolled back)
>
> So, if there's no way to delay the trigger til the transaction
> is committed, then I shall have to go the way of writing
> messages to an alternate table.
>
> Not a big deal, just means that I have to continuously be
> polling this notification table, rather than simply block
> on a read from a pipe.
 

Seems like you're looking for dbms_alert

From the documentation:
DBMS_ALERT supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed.

[...]

Alerts are transaction-based. This means that the waiting session is not alerted until the transaction signalling the alert commits. There can be any number of concurrent signalers of a given alert, and there can be any number of concurrent waiters on a given alert.

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Fri Oct 24 2003 - 14:30:59 CDT