mmatharu
asked on
create a trigger with dblink
I am trying to create this trigger to update the same table "RIGHTS.INTERFACE_USER" in two different databases and they are in the same schema. I have created a public DBLINK "DASHDEV_SND_LNK.CZHS0286"
CREATE OR REPLACE TRIGGER RIGHTS.INTERFACE_USER_DBLI
AFTER INSERT OR UPDATE OR DELETE
ON RIGHTS.INTERFACE_USER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE RIGHTS.INTERFACE_USER@DASH
SET
EMAIL_ADDRESS = :NEW.EMAIL_ADDRESS,
MOBILE_CODE = :NEW.MOBILE_CODE,
PHONE_CODE = :NEW.PHONE_CODE,
USER_NAME = :NEW.USER_NAME,
USER_PASSWORD = :NEW.USER_PASSWORD,
SURNAME = :NEW.SURNAME,
FORENAME = :NEW.FORENAME
WHERE RIGHTS.INTERFACE_USER.INTE
END;
and I am getting this error when I try to compile the trigger.
PL/SQL: ORA-04052: error occurred when looking up remote object
RIGHTS.INTERFACE_USER@DEV_
ORA-00604: error occurred at recursive SQL level 1
ORA-12154: TNS:could not resolve service na
any ideas?
CREATE OR REPLACE TRIGGER RIGHTS.INTERFACE_USER_DBLI
AFTER INSERT OR UPDATE OR DELETE
ON RIGHTS.INTERFACE_USER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
UPDATE RIGHTS.INTERFACE_USER@DASH
SET
EMAIL_ADDRESS = :NEW.EMAIL_ADDRESS,
MOBILE_CODE = :NEW.MOBILE_CODE,
PHONE_CODE = :NEW.PHONE_CODE,
USER_NAME = :NEW.USER_NAME,
USER_PASSWORD = :NEW.USER_PASSWORD,
SURNAME = :NEW.SURNAME,
FORENAME = :NEW.FORENAME
WHERE RIGHTS.INTERFACE_USER.INTE
END;
and I am getting this error when I try to compile the trigger.
PL/SQL: ORA-04052: error occurred when looking up remote object
RIGHTS.INTERFACE_USER@DEV_
ORA-00604: error occurred at recursive SQL level 1
ORA-12154: TNS:could not resolve service na
any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yes that fine.
dblink: is a complete or partial name of a database link to a remote
database where the table or view is located. You can only use a
database link to update a remote table or view if you are using
Oracle with the distributed option.
not sure it applies to your case. Can you update with the dblink in sql (outside the trigger)?
database where the table or view is located. You can only use a
database link to update a remote table or view if you are using
Oracle with the distributed option.
not sure it applies to your case. Can you update with the dblink in sql (outside the trigger)?
ASKER
I've got it working and had to create a specific dblink in the user
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have got them inserting and updating, but would like toknow more about what you mean by "real-time replication, and there are multiple options" as I think I will need to implement this as well
The phrase I had used was: "not quite real-time replication" which is different from "real-time replication". What you have with a trigger is one type of real-time replication. Oracle's replication using materialized views is another way to do replication, and they can be either real-time, or delayed. Another option that we use for replication between Oracle and a non-Oracle system involves PL\SQL-based interfaces that use a database link based on third-party "gateway" software that allows us to read from or write to the non-Oracle system via PL\SQL. We add a flag column to our large Oracle tables that need to be replicated, and set that to a non-null value after inserts or updates (we do not allow deletes from these tables) and we index that flag column with a standard b-tree index. Then our PL\SQL interface procedures simply query the records that need to be replicated (based on having a value > ' ' in the flag column) send them to the other system, then update the flag to null (so the index stays small).
ASKER
Thanks.