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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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.