Re: Monitor inserts on a remote table?

From: <fitzjarrell_at_cox.net>
Date: 15 Feb 2005 19:58:22 -0800
Message-ID: <1108526302.443091.148680@l41g2000cwc.googlegroups.com>

April wrote:
> Is it possible to monitor inserts to a remote Oracle table from
> another Oracle database. The monitoring database is Oracle 9i.
>
> I tried to write a trigger like below that would insert some data
into
> the monitoring database when data was inserted into a remote table.
>
> CREATE OR REPLACE TRIGGER TRG_TEST_REMOTE
> BEFORE INSERT ON BRPR.TBLSUB_INVESTMENT_CASHFLOWS_at_BRPRPRD9.world
>
> BEGIN
>
> insert into TEST_REMOTE_TRG values
> (:new.Investment#,:new.Sub_Investment#,:new.Year, :new.Contract);
>
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK;
> RAISE;
>
> END TRG_TEST_REMOTE;
>
> I get the "ORA-02021: DDL operations are not allowed on a remote
> database" message. However, I am not inserting into the remote table,
> just checking if something was inserted into it through the database
> link.
>
> THanks,
> April

You are confusing DML (inserts, updates, deletes) with DDL (alter table, create or replace trigger, etc.). You cannot perform DDL on a remote database which is exactly what you are attempting to do, which means you cannot create this trigger on your remote table.

You will need to find another way to monitor your inserts on the remote table, such as creating the trigger locally on the remote table you wish to monitor (at BRPRPRD9.world) and insert your values into the desired table on your local table through the link as shown below:

 CREATE OR REPLACE TRIGGER TRG_TEST_REMOTE  BEFORE INSERT ON BRPR.TBLSUB_INVESTMENT_CASHFLOWS  BEGIN    insert into TEST_REMOTE_TRG@<db location of table> values  (:new.Investment#,:new.Sub_Investment#,:new.Year, :new.Contract);

    EXCEPTION

      WHEN OTHERS THEN
        ROLLBACK;

    RAISE;  END TRG_TEST_REMOTE; The insert should succeed as that is allowed activity across a database link.

David Fitzjarrell Received on Tue Feb 15 2005 - 21:58:22 CST