-
Source Database standby in waiting state.
Sir i write a Databbse trigger on Before insert, i want to insert values in another databse on the network. My Code is 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;
Now when the target database (machine) unplug the network cable or restart then the source machine standby in waiting state, and after sometime the following error araise.
ERROR at line 1:
ORA-02068: following severe error from MY_LINK
ORA-03113: end-of-file on communication channel
ORA-06512: at "HANCO.REP_TEST", line 4
ORA-04088: error during execution of trigger 'HANCO.REP_TEST'
Now even the values is not inserting in the source database.
Please help me regarding that.
Thanks
-
so you unplug the network cable and it doesnt work?
What are you expecting to happen?
-
At least value should be insert in saource database
I want if the target databse did not find then source database should not be down. At leat values should be insert in the source database.
-
well thats not going to happen with a before insert trigger is it as the statement fails and everything is rolled back (an autonomous transaction may work though)
-
Re: At least value should be insert in saource database
Originally posted by Sohail Nawaz
I want if the target databse did not find then source database should not be down. At leat values should be insert in the source database.
You need to understand the ACID properties of Oracle. Either the whole transaction works, or it fails and gets rolled back.
-
Using a trigger to update a remote table is not a good idea (now you know why), better look into materialized views or any other of the replication options offered by Oracle.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|