Trigger on shared tables
Stefan Rupp
For a new project we need to interface with a third party product via
shared Oracle tables. Whenever the other product writes a row into a
table, our application has to read the table and process the new data.
For this to work I thought about having an insert trigger on that table
which informs our application about the new data to be read. What
methods exist that I can use inside the trigger to trigger some action
of our application? Is it possible to send a signal to our process?
How do other interfaces work? I don't want to use active polling on the
table.
Regards,
Stefan
Harald Maier
Look at the dbms_alert package. It provides the methods
- signal
- register
- waitone
- and others
Harald
Anurag Varma
"Stefan Rupp" <st....@t-online.de> wrote in message news:bi086g$kkb$01$1...@news.t-online.com...
What process are you talking about?. You need to be more elaborate to expect
a meaningful reply.
Like the other poster replied, signals can be sent via dbms_alerts .. however,
it might be overkill or unsuitable for what you plan to do.
.. and while you are at it .. its wise to post the oracle version/OS when asking questions.
Anurag
Stefan Rupp
Anurag Varma schrieb:
> What process are you talking about?. You need to be more elaborate to expect
> a meaningful reply.
>
> Like the other poster replied, signals can be sent via dbms_alerts .. however,
> it might be overkill or unsuitable for what you plan to do.
>
> .. and while you are at it .. its wise to post the oracle version/OS when asking questions.
You are right, I should have given more details, sorrz about that.
It's for Oracle 9i Rel. 2 on UNIX (both HPUX on IA64 and Linux x86).
The general idea is that the other side of the interface writes a record
in a database table which should trigger our application (a C++ program)
to read the record from the table for further processing. We are
connecting to the database using an old version of the Rogue Wave
DBTools library.
If I understood the hint of the other poster (thank you, Harald Maier)
correctly, our C++ program needs to register a signal using a procedure
from the dbms_alert package and then block in a call to
dbms_alert.waitany to wait for the trigger on that table to throw that
signal using the dbms_alert.signal procedure. Will that be quick enough?
Regards,
Stefan
Harald Maier
As far as I understand, waitone should be enough. But I would not use
'for each row' in the trigger definition and also you should think
about Anruag's words: it might be _overkill_. The trigger will be
executed on commit. Here an fragment.
,----[ signal ]
| create or replace trigger dept_aiud
| after insert or update or delete on dept
| begin
| -- Needs execute rights.
| dbms_alert.signal( 'dept_modify', 'dept is modified' );
| end;
| /
`----
,----[ Test ]
| declare
| proc varchar2(20) := 'dept_modify';
| msg varchar2(256);
| stat number := 0;
| begin
| dbms_alert.register(proc);
| dbms_alert.waitone(proc,msg,stat,60);
| if stat = 0 then
| null; -- do something
| end if;
| dbms_alert.remove(proc);
| end;
`----
Stefan Rupp
Harald Maier schrieb:
> As far as I understand, waitone should be enough. But I would not use
> 'for each row' in the trigger definition and also you should think
> about Anruag's words: it might be _overkill_. The trigger will be
> executed on commit.
Okay, if a trigger with dbms_alert is considered as overkill, what other
mechanisms are more appropriate? I would be grateful for suggestions.
> Here an fragment.
>
> ,----[ signal ]
> | create or replace trigger dept_aiud
> | after insert or update or delete on dept
> | begin
> | -- Needs execute rights.
> | dbms_alert.signal( 'dept_modify', 'dept is modified' );
> | end;
> | /
> `----
>
> ,----[ Test ]
> | declare
> | proc varchar2(20) := 'dept_modify';
> | msg varchar2(256);
> | stat number := 0;
> | begin
> | dbms_alert.register(proc);
> | dbms_alert.waitone(proc,msg,stat,60);
> | if stat = 0 then
> | null; -- do something
> | end if;
> | dbms_alert.remove(proc);
> | end;
> `----
Thanks. It's similar to what I tried this afternoon.
Regards,
Stefan
Harald Maier
Stefan Rupp <st....@t-online.de> writes:
> Good evening,
>
> Harald Maier schrieb:
>> As far as I understand, waitone should be enough. But I would not use
>> 'for each row' in the trigger definition and also you should think
>> about Anruag's words: it might be _overkill_. The trigger will be
>> executed on commit.
>
> Okay, if a trigger with dbms_alert is considered as overkill, what
> other mechanisms are more appropriate? I would be grateful for
> suggestions.
Personally, I think if you have moderate modifications to the table
then it should be ok. If you have lots of committed changes to the
table then why not poll the table. Another possibility would be to
combine both methods. One loop listens to the signal. Another loop
updates the viev.
Harald
Anurag Varma
"Harald Maier" <mai...@myself.com> wrote in message
news:m3zni2g...@ate.maierh...
.. additing to this. If the inserts/updates are that frequent, why not just
call the C++ function via the trigger? You can do that also.
Anurag
Anurag Varma
"Anurag Varma" <avarmadba...@yahoo.com> wrote in message
news:yuu1b.479$Jq1...@nwrddc03.gnilink.net...
Also look at the thread above named "dbms_alerts on multiple updates"
for ideas posted by others.
Thanks,
Anurag
Stefan Rupp
Anurag Varma schrieb:
> .. additing to this. If the inserts/updates are that frequent, why not just
> call the C++ function via the trigger? You can do that also.
How do I do that? Pardon me for my poor understanding of Oracle
mechanisms, I have only used it as a storage manager so far.
How do I call a C or C++ function from within an Oracle trigger?
Regards,
Stefan
Anurag Varma
external procedures:
Go to http://tahiti.oracle.com
The documentation is out there (books).
In that see the "Application Developers Guide - Fundamentals"
The specific link is:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg11rtn
.htm#1000718
That tells the basics .. and then points you to where you can find more
details.
You'd need a free registration.
hth
Anurag
Anurag Varma
Also remember: If you call an external procedure via a trigger .. and if the
original transaction fails -
Then whatever you do in that external procedure will not be rolled back. Be
aware of that!
This problem will be there in the dbms_alert also.
Since I do not recall doing a transaction using an external procedure, I do
not know of the pitfalls of
using this method. Test Test Test...
You can probably trigger a polling to be done using the trigger (i.e. send
an async signal to the external procedure
to poll the actual table .. and make that trigger to be after insert and
statement level trigger)
Anurag
Anurag
Stefan Rupp
Anurag Varma schrieb:
> Also remember: If you call an external procedure via a trigger .. and if the
> original transaction fails -
> Then whatever you do in that external procedure will not be rolled back. Be
> aware of that!
>
> This problem will be there in the dbms_alert also.
>
> Since I do not recall doing a transaction using an external procedure, I do
> not know of the pitfalls of
> using this method. Test Test Test...
All that proves your initial warning: it may be overkill.
> You can probably trigger a polling to be done using the trigger (i.e. send
> an async signal to the external procedure
> to poll the actual table .. and make that trigger to be after insert and
> statement level trigger)
That's almost what I had in mind in the first place. But with your (and
Harald's) input I need to rethink again if a simple polling of the table
might be a better solution, since this requires much effort in testing
(race conditions etc.). Polling is probably easier to implement (and test).
Thank you very much indeed for your help!
Stefan