> Applications that listen to events are meant to run all the time in
> the background and "wake up" when event fires. Since they run all
> the time, they can keep the state and know where they "left off".
> For example, instead of querying all the tasks completed today, each
> time you get the event and process it, save CUREENT_TIMESTAMP in
> some local variable of the application. Next time event fires, just
> query
>
> WHERE update_time > :last_time
>
> to get records that were updated since. This is one type of
> application.

Be aware that in a multi-user environment there are some difficulties and it
is possible to miss some rows with this approach unless carefully
implemented. Consider the following scenario (assuming that the monitoring
application that receives the event notifications does not want to process
uncommitted changes in case the client rolls back the transaction):

10:00:00 Monitoring application sees server CURRENT_TIMESTAMP (10:00:00)
and saves it locally as last_time [A].
10:01:00 Client 1 starts a transaction and starts updating rows and
setting update_time to CURRENT_TIMESTAMP (10:01:00...)
10:01:10 Client 2 starts a transaction and starts updating some other
rows and setting update_time to CURRENT_TIMESTAMP (10:01:10...)
10:01:15 Client 2 commits the transaction, event is fired
10:01:16 Monitoring application receives event, starts a transaction,
processes committed rows where update_time > last_time [A] (10:00:00),
retrieves CURRENT_TIMESTAMP (10:01:16) and saves as new checkpoint
last_time [B], commits transaction
10:01:20 Client 1 commits the transaction, event is fired
10:01:21 Monitoring application receives event, starts a transaction,
processes committed rows where update_time > last_time [B] (10:01:16),
retrieves CURRENT_TIMESTAMP (10:01:21) and saves as new checkpoint
last_time [C], commits transaction

This fails in that updated rows committed by Client 1 with an update_time
between 10:01:00 (the time it started the transaction) and 10:01:16 (the new
checkpoint for the monitoring app) will be missed as Client 1 committed the
transaction at 10:01:20, after the monitoring app updated its last_time
checkpoint. Only those with an update_time after 10:01:16 will be processed
in the second case.

There are more complex methods that will work but I just wanted to point out
that a simple method that looks ok on the surface might not.

Regards,

Jarrod Hollingworth
Complete Time Tracking
http://www.complete-time-tracking.com/