Step 1:
--------
I have one database called SALES. Test is the user into that and have one
table called ITEM.
Table : ITEM
ITNO number(10)
Name varchar2(10)
Step : 2
---------
I have another database called ACCT. Test1 is the user and have one table
called RATE.
Table : RATE
ITNO number(10)
Rate varchar2(10)
Step :3
--------
I create a database link sitting on SALES database.
SQL> Create database link acct.orillion.com connect to test1 identified by test1 using 'acct';
Step:4
-------
I create a public synonym for table Rate.
SQL> create public synonym rate for rate@acct.orillion.com;
Step :5
--------
I can do any insert,select, update ,delete on that synonym from TEST user of SALES database.
***********
STEP :6 ( The problem occurs here)
---------------------------------------
Then I create a trigger on ITEM table as TEST user on SALES database.
create or replace trigger item_ins
after insert on item
for each row
begin
insert into rate values(:new.itno,10);
end;
/
Here it's giving me the following error. ( In PL/SQL block, procedure the same prob coming.)
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
2/13 PL/SQL: ORA-00980: synonym translation is no longer valid
If I mention in the trigger
====================
insert into rate@acct.orillion.com values( :new.itno,10) from TRIGGER it's not giving me error.
How to solve this problem from trigger,procedure or blocks?
Early response will be appreciated.
Thanks and Regards
Sumit
