sybase 表数据同步
来源:互联网 发布:mysql的sql语句大全 编辑:程序博客网 时间:2024/06/08 14:21
环境:
server name : test1
database name : test1
table name : table11
server name : test1
database name : test1
table name : table11
server name : test2
database name : test2
table name : table21
database name : test2
table name : table21
dsedit 加入同步服务器设置
1> exec sp_addserver test2, ASEnterprise, test2 ######添加远程服务器信息
2> go
Adding server 'test2', physical name 'test2'
Server added.
(return status = 0)
1> exec sp_addexternlogin test2, sa, sa , sybase ######添加远程登陆信息
2> go
User 'sa' will be known as 'sa' in remote server 'test2
(return status = 0)
1> use test11
2> go
1> create proxy_table table21 at "test2.test21.dbo.table21" ######创建代理表
2> go
1> select * from table21 ######可访问远程服务器表
2> go
id name
----------- ----------
2 alvin2
(1 row affected)
1> CREATE TRIGGER tr_table11 ######创建触发器同步数据
2> ON table11
3> FOR INSERT,UPDATE,DELETE
4> AS
5> IF NOT EXISTS(SELECT * FROM deleted) AND EXISTS(SELECT * FROM inserted)
6> BEGIN
7> INSERT INTO table21 SELECT * FROM inserted
8> END
9> ELSE IF EXISTS(SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
10> BEGIN
11> DELETE FROM table21 WHERE id IN (SELECT id FROM deleted)
12> END
13> ELSE IF EXISTS(SELECT * FROM deleted) AND EXISTS(SELECT * FROM inserted)
14> BEGIN
15> DELETE FROM table21 WHERE id IN (SELECT id FROM deleted)
16> INSERT INTO table21 SELECT * FROM inserted
17> END
18> GO
1> insert into table11 values(1,"alvin")
2> go
(1 row affected)
1> select * from table21
2> go
id name
----------- ----------
1 alvin
2> ON table11
3> FOR INSERT,UPDATE,DELETE
4> AS
5> IF NOT EXISTS(SELECT * FROM deleted) AND EXISTS(SELECT * FROM inserted)
6> BEGIN
7> INSERT INTO table21 SELECT * FROM inserted
8> END
9> ELSE IF EXISTS(SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
10> BEGIN
11> DELETE FROM table21 WHERE id IN (SELECT id FROM deleted)
12> END
13> ELSE IF EXISTS(SELECT * FROM deleted) AND EXISTS(SELECT * FROM inserted)
14> BEGIN
15> DELETE FROM table21 WHERE id IN (SELECT id FROM deleted)
16> INSERT INTO table21 SELECT * FROM inserted
17> END
18> GO
1> insert into table11 values(1,"alvin")
2> go
(1 row affected)
1> select * from table21
2> go
id name
----------- ----------
1 alvin
(1 row affected)
1> insert into table11 values(2,"alvi")
2> go
(1 row affected)
1> insert into table11 values(3,"alv")
2> go
(1 row affected)
1> commit
2> go
1> select * from table21 ######插入数据同步成功
2> go
id name
----------- ----------
1 alvin
2 alvi
3 alv
1> insert into table11 values(2,"alvi")
2> go
(1 row affected)
1> insert into table11 values(3,"alv")
2> go
(1 row affected)
1> commit
2> go
1> select * from table21 ######插入数据同步成功
2> go
id name
----------- ----------
1 alvin
2 alvi
3 alv
(3 rows affected)
1> delete from table11 where id=1
2> go
(1 row affected)
1> select * from table21 ######删除数据同步成功
2> go
id name
----------- ----------
2 alvi
3 alv
1> delete from table11 where id=1
2> go
(1 row affected)
1> select * from table21 ######删除数据同步成功
2> go
id name
----------- ----------
2 alvi
3 alv
(2 rows affected)
1> commit
2> go
1> update table11 set name="ultra" where id=2
2> go
(1 row affected)
1> select * from table21
2> go
id name
----------- ----------
3 alv
2 ultra
1> commit
2> go
1> update table11 set name="ultra" where id=2
2> go
(1 row affected)
1> select * from table21
2> go
id name
----------- ----------
3 alv
2 ultra
(2 rows affected)
1> update table11 set name="ultra" where id=3
2> go
(1 row affected)
1> commit
2> go
1> select * from table21 ######更新数据同步成功
2> go
id name
----------- ----------
2 ultra
3 ultra
1> update table11 set name="ultra" where id=3
2> go
(1 row affected)
1> commit
2> go
1> select * from table21 ######更新数据同步成功
2> go
id name
----------- ----------
2 ultra
3 ultra
(2 rows affected)
1> delete from table11 where id=2
2> go
(1 row affected)
1> select * from table21
2> go
id name
----------- ----------
3 ultra
1> delete from table11 where id=2
2> go
(1 row affected)
1> select * from table21
2> go
id name
----------- ----------
3 ultra
(1 row affected)
1> delete from table11 where id=3
2> go
(1 row affected)
1> commit
2> go
1> select * from table21
2> go
id name
----------- ----------
1> delete from table11 where id=3
2> go
(1 row affected)
1> commit
2> go
1> select * from table21
2> go
id name
----------- ----------
(0 rows affected)
缺点:当备库出现问题的时候 ,会影响主库。
- sybase 表数据同步
- Sybase IQ 数据库数据互访和同步
- 关于sybase Mobilink同步
- Sybase iAnywhere同步问题
- 数据检索-多表查询(sybase)
- Sybase数据库超大表数据矫正更新
- SYBASE数据导入技巧
- sybase数据管道使用方法
- sybase数据操作客户端
- 利用Sybase Central 导出与导入表内数据
- Sybase PowerDesigner 16.5.0 带数据修改表结构
- kettle表数据同步
- kettle表数据同步
- Sybase的bcp备份数据
- 数据库-数据更新-触发器(SYBASE)
- 数据更新-存储过程-SYBASE
- sybase iq选择topN数据
- Sybase数据导入导出命令
- 分布式云数据中心解决方案
- silverlight 滚动文字
- Eclipse中Cannot find any provider supporting DES解决之道
- Oracle 内连接和外连接
- Matlab 函数 sparse & full
- sybase 表数据同步
- Bean 字段生产xml
- 中国空“芯”之忧:一年进口芯片总值超石油
- Java几款性能分析工具的对比
- 登录后返回登录前的页面
- c++关键字explicit
- SQL2008 --不允许保存更改。您所做的更改要求删除并重新创建以下表......
- C++错误提示英汉对照表
- Java String的序列化小结