So basically you are asking for inter process communication between the database engine and your app. For a "push" approach your idea with a trigger-based seems reasonable, as only triggers can immediately react upon DML statements like an UPDATE. If updates to the accordings tables are infrequent, you might trigger a push notification for each update, if they are more common, you will probably use some kind of delay to prevent too frequent notifications. Using an database event (CREATE EVENT...) that runs on its own connection and may "wait a while" before doing the push notification may help here without delaying the actual UPDATEs.
Of course you could also use a "polling" approach by making the app regularly query the database state. Besides using your own "state table", you could also provide the "state" via an (immediately or manually) refreshed materialized view - the database would not necessarily require triggers to "set a state" but would do this autmatically by refreshing a view. And the app could check via SYSVIEW.mv_last_refreshed_at (or mv_last_refreshed_at_utc) whether the contents has changed. |
Some questions:
It would help to tell more what you want to achieve...
Hello Volker, Thanks so much for your response. 1) Yes, the cpp windows service app is connected to the database via a dll using sqlA's ODBC_API. Any windows server OS(windows 2012/6/9). 2) No, just an update on the table rows. The app just consumes the data from the rows, there is no display in a view. 3) yes the app and the db engine(as a service) is running on the same system.
What I want to achieve is that some of the triggers and stored procs update a particular table based on application input/needs. Some of the other services consuming the cache(yet to be designed) need to be notified if and when the table is updated, so that the cache(containing table's data) can be flushed out and recreated.
one way of achieving the goal is from with in the app/s as and when the funtions are called to update/insert other tables leading to this update.
I also believe that the table's after update trigger can call a function with in the DB which can accomodate an external name with the dll and the associated function(dll name.dll::className.func() with any parameter.
Please comment, I am open to any new ideas or critic the above... please also let me know if any additional clarifications are needed...
thanks so much and best regards.