DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Need Help in Trigger Timing.

  1. #1
    Join Date
    Apr 2005
    Location
    Saudi Arabia
    Posts
    13

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: Need Help in Trigger Timing.

    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;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width