Re: Using Triggers w/ ORACLE7
Date: 1995/04/19
Message-ID: <D7AwC6.Gp_at_uunet!aadt>#1/1
In article <3lsb76$2q9_at_portal.gmu.edu>, ndreon_at_osf1.gmu.edu (Nathan Dreon) writes:
|
|> Look at page A-15 to A-25 of Oracle7 Server Application Developer's
|> Guide. It explains how to use triggers, alerts, and pipes to have
|> Oracle send events and data to a C program. I tried to do this
|> but had no luck. If anyone has a small working example I would love
|> to see it. I think I had a problem with database permissions.
|>
|> Good Luck
|>
|> Nathan
|>
This seems to me as a very common problem for real time applications. I looked up pages A-15 to A-25 of Oracle7 Server Application Developer's and here are my experiences with different methods.
- DBMS_PIPE package can be used to send changed information from a trigger to an external process. External process is a daemon listening on a pipe for data.
Problems: Triggers execute before commit takes place. So if you update or insert a row trigger is executed and changed data is sent out on pipe. If now you decide to rollback, you can nullify the change in the database but there is now way to get the data back from pipe. This was not acceptable for my application.
2. DBMS_ALERT package works like signals in Unix. One can define alerts to send out a set of data to an external process when an event takes place. An event can be insert/update on a table. This is invoked after the commit. I tried to sent a message containing changed table_name/row data to a external process.
Problems: DBMS_ALERT package works like Unix signals. If there are changes in more than one row for a given alert, alert is invoked only once. So if you are sending changed row data, you may potentially miss some updates. But alert can provide you with an excellent mechanism to decide when to go poll tables (which change infrequently).
Piyush
bhargava_at_sdt.com
Opinions above are my own and not those of my employer. Received on Wed Apr 19 1995 - 00:00:00 CEST