Roman Rokytskyy wrote:

>>- thanks a lot for useful information, but for now (because of hastiness
>>:-( , I am looking for a good caching strategy with regard on db data
>>changes.
>>
>>
>
>I am not sure if event can help you. They tell only that some event is
>happened (for example, that some table is changed), but they do not tell
>which records are changed. In this case you would need to re-read complete
>table.
>

Roman, that's silly. A program that what wants notification of a stock
change doesn't wait on a generic event like "stock changed" but
something specific like "ibm". Other applications may indeed want
generic events, but they're backed up with log or "to do" tables.

Passing data associated with events vastly increases the amount of event
state maintained by the server, the amount of network traffic,
complicates application design, and ultimately doesn't gain you anything.

>
>If you assume that rollbacks are rare in your system, you can write an UDF
>that will send some information via network sockets and call this UDF in
>each AFTER UPDATE/DELETE trigger. There will be false notifications for the
>changes that are rolled back, but if the rate is low, it will not affect
>your application.
>

Roman, Roman, Roman! I am not a complete fool. Events are under
transaction control and posted to the event manager *after* a
transaction commits. They can't work any other way, actually, since the
data that reflects the event isn't available to other transactions until
the posting transaction completes.

>
>
>
>>So, how to properly detect database data changes, and invalidate cached
>>data in the application ?
>>
>>
>
>Let all data changes go through a middle tier, cache them there and
>invalidate them there?
>
>
>

Events. They are only alternative to polling the database.