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?