Trigger to fire on Remote Database

[Kishore]

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??

[kkpareek]

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

[Kishore]

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.

[Kishore]

Any suggestions please

[jmodic]

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:

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.

[Kishore]

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?