[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

create a trigger with dblink

Posted on 2005-04-26
9
Medium Priority
?
3,318 Views
Last Modified: 2008-02-01
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" e.g.

CREATE OR REPLACE TRIGGER RIGHTS.INTERFACE_USER_DBLINK_TRG
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@DASHDEV_SND_LNK.CZHS0286  
    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.INTERFACE_USER_ID = :NEW.INTERFACE_USER_ID;
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_TO_TST.CZHS0286
ORA-00604: error occurred at recursive SQL level 1
ORA-12154: TNS:could not resolve service na

any ideas?
0
Comment
Question by:mmatharu
  • 4
  • 2
  • 2
8 Comments
 

Author Comment

by:mmatharu
ID: 13867830
the reason why I am doind this is when the table in DEV is update or new rows inserted, this will update TST, to keep them in sync.

Thanks.
0
 
LVL 5

Accepted Solution

by:
Zopilote earned 800 total points
ID: 13867938
Can you do a simple select?

select *
from RIGHTS.INTERFACE_USER@DEV_TO_TST.CZHS0286;

0
 

Author Comment

by:mmatharu
ID: 13867962
yes that fine.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 5

Expert Comment

by:Zopilote
ID: 13868045
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)?
0
 

Author Comment

by:mmatharu
ID: 13868220
I've got it working and had to create a specific dblink in the user
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 600 total points
ID: 13868451
When you say "I've got it working" does that mean your problem is solved, or does that mean you have a database link working, but still have trouble with the trigger?

Even if you have the trigger working (which should be possible) this may not be a good solution.  The disadvantage of this trigger is the fact that if the remote database is down, or if the network is down, then updates will not be allowed in the DEV database.  Is that acceptable?  If not, you will need some kind of not quite real-time replication, and there are multiple options for that.
0
 

Author Comment

by:mmatharu
ID: 13885324
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
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 13893815
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).
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month17 days, 16 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question