Trigger to fire on Remote Database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Trigger to fire on Remote Database

  1. #1
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Hi

    can we write a trigger to fire on remote database. I established Database link. Here is what I am trying to do.



    Table test should get updated when ever there is an entry on test2 from remote database


    My Question:

    Is it possible to access table from remote database using Database link in trigger??

    Thanks
    Kishore Kumar

  2. #2
    Join Date
    Feb 2001
    Posts
    75

    remote trigger

    Hi Kishore,

    I do not see why not. Oracle provides the complete transparency. So whatever you can do in local, you can pretty much do in remote too, provided if you have correct previleges.

    Kailash pareek

  3. #3
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Hi

    I tried creating the tirgger on TEST2 table on SERVER2 from the SERVER1.

    create trigger REMOTE after update or insert on TEST2@SERVER2
    .....
    It says DDL is not allowed on REMOTE server..

    any solution for my problem.

    I need to create a trigger on table from SERVER2 which RUNS from shema from SERVER1.
    Thanks
    Kishore Kumar

  4. #4
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Any suggestions please
    Thanks
    Kishore Kumar

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Kishore
    create trigger REMOTE after update or insert on TEST2@SERVER2 .....
    It is not quite clear what are you trying to achive. As far as I understand you want to have a trigger on database A that would fire on DML on the table in the database B. This is neither logical nor doable requirenment.

    Remember that DML trigger is a database object that is tightly bound to a particular table. So if table TEST2 is on SERVER2 it is logical and only possible that a trigger on that table must be created on SERVER2. Why would you want to create trigger on TEST2@SERVER2 from SERVER1? Simply connect to SERVER2 and create trigger there:
    Code:
    create trigger REMOTE after update or insert on TEST2 .....
    Now if you want to perform some actions on SERVER1 from within a trigger, you simply use db link from SERVER2 to SERVER1 in your trigger's code.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Jeff,

    Thanks for the reply. I found that it is not possible to do DML operation I on some other server.

    For business reasons the SERVER2 will be wiped out with SERVER1 every night and so is the trigger on SERVER2. My requirement is, I need to update transactions of one table from server2 to server1 so that when the server2 wipes out I will have that tables data on SERVER1.

    The other way of doing it is, I am creating the Database link on SERVER1 connectes to SERVER1 only, but when it is transferred to SERVER2 on the night, the trigger will update the table on SERVER1 using the database link. But here the problem is UNLESS I set the GLOBAL_NAME=FALSE I cannot do that. So I made it false using ALTER SESSION command. But is not effective on other users.

    Do I need to restart the databse?

    Thanks
    Kishore Kumar

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