Hi, I'm trying to do some error handling in a database (db_y) trigger . I have a db link to another oracle db (db_x). If that db (db_x) is down and the insert fails I want the transaction to continue and update it in a table in it's own db (db_y) and not roll the transaction back. Is this possible? Any help would be greatly appreciated. Thanks in advance
CREATE OR REPLACE TRIGGER CRPDTA.MG_F4102 AFTER INSERT OR UPDATE ON CRPDTA.F4102
REFERENCING old as old new as new
FOR EACH ROW
DECLARE
INVALID_INSERT EXCEPTION;
Begin
IF INSERTING and :new.IBMCU=lpad('STDCAN',12) and :new.IBSRP2 in('HC9','HC3','HC1') and trim(:new.IBSRP6) = 'PKG' then
insert into CRPDTA.F55H4102
(
BRMCU,
BRITM,
BRLITM,
BREDSP,
BRPID,
BRUSER,
BRUPMJ ,
BRUPMT)
Values
(
:new.IBMCU,
:new.IBITM,
:new.IBLITM,
'N',
:new.IBPID,
:new.IBUSER,
:new.IBUPMJ,
:new.IBTDAY
IF UNABLE TO INSERT INTO DB_X, I WANT TO BE ABLE TO CATCH THE ERROR AND DO THE INSERT INTO A TABLE IN DB_Y
);
end if;
End;
/