Re: Trigger on shared tables

From: Harald Maier <maierh_at_myself.com>
Date: Thu, 21 Aug 2003 19:46:01 +0200
Message-ID: <m3he4ahpk6.fsf@ate.maierh>

Stefan Rupp <st.rupp_at_t-online.de> writes:

> Good afternoon,
>
> 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?

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;

`---- Received on Thu Aug 21 2003 - 12:46:01 CDT