Need Help in Trigger Timing.

I want to replicate the values in another database
through trigger. when then traget database fail. suppose netwrok cable unplug and restart the traget machine, then statement hang up, can u specifie there time.
Here is my code below


CREATE OR REPLACE TRIGGER "REP_TEST" AFTER INSERT OR DELETE OR UPDATE OF EMPNO, ENAME ON TEST
REFERENCING NEW AS N OLD AS O FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TEST@MY_LINK(EMPNO,ENAME)
VALUES (:N.EMPNO,:N.ENAME);
ELSIF UPDATING THEN
UPDATE TEST@MY_LINK
SET EMPNO = :N.EMPNO,ENAME = :N.ENAME
WHERE EMPNO = :O.EMPNO;
ELSIF DELETING THEN
DELETE FROM TEST@MY_LINK
WHERE EMPNO = :O.EMPNO;
END IF;
END;

WHEN THE TARGET MACHINE FAIL THEN THE STATEMENT WILL FAIL.I WANT TO SPECIFIE TIME THERE. IF WITH IN SPECIFIED TIME TARGET MACHINE FIND THEN ITS OK OTHERWISE WILL SHOULD BE FAILED/

THANKS

sOHAIL

You can try this, but I did not test the code. You can also submit the package by using DBMS_JOB/DBMS_SCHEDULER in 10g. But you might want to look at replication. In the very least if you are updating a lot of columns you may want to pass the variables with a row level variable.

Code:

---

CREATE OR REPLACE TRIGGER rep_test
AFTER INSERT OR DELETE OR UPDATE
OF empno, ename ON test FOR EACH ROW
BEGIN
 IF INSERTING THEN
  update_test_at_mylink
  ( 'I', :NEW.empno,:NEW.ename);
 ELSIF UPDATING THEN
  update_test_at_mylink
  ( 'U', :NEW.empno,:NEW.ename);
 ELSIF DELETING THEN
  update_test_at_mylink
  ( 'D', :OLD.empno );
 END IF;
END;
/

CREATE OR REPLACE PROCEDURE update_test_at_mylink
  ( p_dml_type IN CHAR,
    p_empno    IN my_table.empno%TYPE,
    p_ename    IN my_table.ename%TYPE  DEFAULT NULL)
AS
  PRAGMA AUTONOMOUS TRANSACTION;
BEGIN
  IF p_dml_type = 'I'
  THEN
      INSERT INTO test@my_link(empno,ename)
      VALUES (p_empno,p_ename);
  ELSIF p_dml_type = 'U' THEN
      UPDATE test@my_link
        SET empno = p_empno,
            ename = p_ename
      WHERE empno = p_empno;
  ELSIF p_dml_type = 'D' THEN
      DELETE test@my_link
      WHERE empno = p_empno;
  END IF;
  COMMIT;
EXCEPTIONS
  WHEN OTHERS THEN
      -- Call error logging package
      NULL;
END;
/

---