Oracle Autonomous Trigger

[Greg8202]

Hi

I there a way to make a trigger completely independent from the calling table so that even if the trigger is made invalid, inserts can still be done to the table that calls the trigger?

I created the following trigger:

CREATE OR REPLACE TRIGGER LOCAL.SYNC_TRIGGER
AFTER INSERT ON LOCAL.LOCAL_TABLE FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  ecode        NUMBER;
  emesg        VARCHAR2(200);
BEGIN
  -- Code to setup data inserted into remote table omitted ...
  --
  --

  INSERT INTO REMOTE_TABLE@REMOTE_DB 
  (
     SEQ,
     COL1,
     COL2,
     ...
  )
  VALUES
  (
     REMOTE_TABLE_SEQ.NEXTVAL@REMOTE_DB, 
     :new.col1,
     :new.col2,
     ...   );
   COMMIT;
   
   EXCEPTION
    WHEN OTHERS THEN
        ecode := SQLCODE;
        emesg := SQLERRM;
        INSERT INTO LOCAL.TRIGGER_EXCEPTIONS (ERROR_DES) VALUES (to_char(ecode)||'-'||emesg);
        COMMIT;
END;
/

The trigger works fine but there were problems recently with users not being able to capture in the local table. It seems some maintenence was done on the remote database and the trigger appeared invalid in Toad. I recompiled the trigger which solved the problem. The following error was found in the TRIGGER_EXCEPTIONS table:
ORA-02068: following severe error from REMOTE_DB ORA-01033: ORACLE initialization or shutdown in progress

What can I do to make our local operation unaffected by what happens remotely on the remote DB?

[debasisdas]

The problem is not with the trigger but the remote database. Since you are working over a DB link , the remote DB should always be available from the calling / referring DB.

[Greg8202]

I know that the problem is the remote DB but I have no control over that. Is there anything I can do my side to make the syncing more robust to handle the remote DB becoming unavailable.

[debasisdas]

So the problem is since you are referring to the remote DB in the INSERT statement inside the trigger, the trigger becomes invalid once the remote DB is not available / goes off line.

What about trying to use EXECUTE IMMEDIATE for the insert statement inside the trigger !