Oracle 多数据库的数据同时更新

1.创建dblink

create database link test connect to mall identified by test using '
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 113.116.216.60)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
';

2.创建同义词

create or replace synonym store for sale_record@mall; 

执行以下sql可以查看远程数据库对应表的内容：

select * from store;

3.创建表，索引

create table sale_record(
	id int primary key, 
	storeId int not null, 
	saledate date not null, 
	productId int, 
	num varchar(30) not null
);

CREATE SEQUENCE sale_record_seq
INCREMENT BY 1 -- 每次加几个
START WITH 1000000000 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;

4.创建触发器

create or replace trigger addSaleRecords
before insert on sale_record
for each row 
declare 
  -- local variables here   
	v_ID varchar(10); 
	v_storeId number(10);   
	v_saledate date;
	v_productId number(10);
	v_num varchar(40);
begin
	select sale_record_seq.nextval into :new.id  from dual;
	v_ID := :new.id;  
	v_storeId := :new.storeId;
	v_saledate := :new.saledate;
	v_productId := :new.productId;
	v_num := :new.num;
	insert into sale_record@mall(id, storeId, saledate, productId, num) values(v_ID, v_storeId, v_saledate, v_productId, v_num); 
end addSaleRecords; 

测试：

在本地表中添加数据：

insert into sale_record(id, storeId, saledate, productId, num) values(sale_record_seq.nextval,10000001, '2013-12-12 12:12:12', 900000001, 11);

commit;

执行：

SQL> conn test/test@113.116.216.60:1521/orcl
SQL> select * from sale_record

可以看到远程数据库中也被添加了同样的一条数据。