Re: Using Triggers in Pro* C/C++

Hello,

There are 2 ways to get updates to an information list: polling or notification. Polling is
when you explicitly ask for information. Notification is when you set up a handler to
be called when an update occurs. The second is not available in Pro*C, and you will
have to use polling anyway to get updates.

There is a way to emulate notification in Pro*C, though. Do do this, you must make use of DBMS_ALERT package.
Here is the example algorythm, amend it as needed. You register an alert with DBMS_ALERT.REGISTER call.
Then you read a table and show the data, say on the screen. Then periodically you call WAITONE with zero timeout, to avoid waits. In the database you create a insert/update/delete trigger on a table which uses DBMS_ALERT.SIGNAL() to signal you that the table has changed. When this happens, the WAITONE call mentioned before will return with an indication of an update. You can now go and get new information from the table.

To further improve this, note that SIGNAL allows you to pass a string as a parameter. It is possible to pass an ROWID of a changed row, so that you can get notified which row exactly has changed. You may then process changes for changed rows only. The algorythm will get more complicated. The trigger used to track changed rows should be created with FOR EACH ROW clause (be a row trigger), because you want get notified for each row, while if you read an entire table, you will need a statement trigger.

Notice that the described scheme still uses polling, though emulates the effect of notification. Notice also that you could use DBMS_PIPE instead of DBMS_ALERT. However, DBMS_ALERT is logically more correct, because it is
transaction-based. With DBMS_PIPE you will receive a notification even if the change was rolled back. With DBMS_ALERT, you will get the change only if it succeded.

Hope this helps!
Karen Abgarian.

udayavar_at_my-deja.com wrote:

> Hello,
> Is there a way to use Triggers in Pro* C/C++ ?.
> I have written a application which fetches list
> of records from the specified table using embedded SQL.
> And if that table is updated by some one I want to be notified
> so that I can go and fetch the same table again. That is, I am
> trying to emulate the list session on that table.
> I would realy appriciate if some one gives reference or example
> code for using triggers in embedded SQL.
>
> Thanks a lot
>
> --
> kailas
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Aug 18 1999 - 16:06:29 CDT