在SQL中 如何实现不同数据库的两张表的同步

Question

有一台服务器，SQL中有DB_01 和DB_02两个数据库，其中的表 PA，结构完全一样，用什么方法能实现两张表的同步？比如我在DB_01.PA中录入数据，在DB_02.PA中也能看到？

请高手详述！ 谢谢
我查过 也知道用触发器，但具体语句不知道怎么写。

已测试了大家提供的语句 同步增加的触发器 按大家的语句输入后都出现一个

错误 311：不能在'inserted'表和'deleted'表中使用text、ntext或image列。

请问该如何解决啊？

Answer 1

你可以分三个来写,分别用来监视insert\delete\update三个(这样写容易完成),我下面写一个,你其它有可以参照下面这个来写
--插入行的情况
CREATE TRIGGER [填入触发器名] ON [dbo].[表名]
FOR INSERT
AS

insert [另外一个数据库名].[dbo].[表名] select * from inserted

你也可以参照料我下面这段,写在一起,但比较麻烦,我就不具体按你要求的写了罗.
CREATE trigger [数据库A.tr_user] on [user]
/* 触发器 在数据库A的user表建立一个名字tr_user的触发器 */
for update,insert,delete
/*监视 修改 插入 删除*/
as
if not exists (select * from deleted)
/* 如果deleted表为空,那么 */
insert 数据库B..[user](username,userpass,landtime) select username,password,lastlogin from inserted
/* 将inserted表(就是对于触发器来说刚刚被插入的集合)插入到B.user */
else if not exists (select * from inserted)--删除
/* 否则 如果 inserted集合为空 */
delete 数据库B..[user] where id in (select userid from deleted)
/* 那么删除B.user下id是deleted集合中出现的id 这里用了in */
else--更新
update [user]
set
[user].username=i.username,
[user].userpass=i.password
from 数据库B..[user] as [user],
inserted as i
where [user].id=i.userid
/* update就很明显了,凡是updated的都来更新,保持一样就可以了 */
alter table 数据库B..[user] ENABLE TRIGGER [数据库B.tr_user]

Answer 2

首先你要考虑应用的是什么SQL，虽说ORACLE, SQLSERVER等都不同， 但都是大同小异而已，还有就是你所需的处理是输入，更新，还是删除。我这里以SQL SERVER的触发器作为例子

同时输入：

CREATE TRIGGER TRIGGERNAME ON DB_01.PA
AFTER INSERT AS INSERT INTO DB_02.PA(FIELD1,FIELD2, FIELD3...) SELECT * FROM INSERTED

同时删除：
CREATE TRIGGER TRIGGERNAME ON DB_01.PA
AFTER DELETE AS DELETE FROM DB_02.PA WHERE DB_02.PA. FIELD1 = (SELECT FIELD1 FROM DELETED )

同时更新：

CREATE TRIGGER TRIGGERNAME ON DB_01.PA AFTER UPDATE AS
UPDATE DB_02.PA SET DB_02.PA.FIELD2 =
(SELECT FIELD2 FROM DB_01.PA WHERE DB_02.PA.FIELD1 = DB_01.PA.FIELD1)

这里假定要更新的数据为FIELD2，而参照用的
为FIELD1 当然你可以自己任意决定

TRIGGERNAME 为触发器的名字，任意输入

Answer 3

直接可以用了，不过这里我假设主键是id

CREATE TRIGGER tr1
ON DB_01.dbo.PA
FOR INSERT
AS
BEGIN
insert into DB_02.dbo.PA select * from Inserted i
END

CREATE TRIGGER tr2
ON DB_01.dbo.PA
FOR delete
AS
BEGIN
delete DB_02.dbo.PA
from DB_02.dbo.PA,deleted
where DB_02.dbo.PA.id=deleted.id
END

CREATE TRIGGER tr3
ON DB_01.dbo.PA
FOR update
AS
BEGIN
delete DB_02.dbo.PA
from DB_02.dbo.PA,deleted
where DB_02.dbo.PA.id=deleted.id
insert into DB_02.dbo.PA select i.* from inserted i
END

CREATE TRIGGER tr4
ON DB_02.dbo.PA
FOR INSERT
AS
BEGIN
insert into DB_01.dbo.PA select * from Inserted i
END

CREATE TRIGGER tr5
ON DB_02.dbo.PA
FOR delete
AS
BEGIN
delete DB_01.dbo.PA
from DB_01.dbo.PA,deleted
where DB_01.dbo.PA.id=deleted.id
END

CREATE TRIGGER tr6
ON DB_02.dbo.PA
FOR update
AS
BEGIN
delete DB_01.dbo.PA
from DB_01.dbo.PA,deleted
where DB_01.dbo.PA.id=deleted.id
insert into DB_01.dbo.PA select i.* from inserted i
END

Answer 4

建一个触发器,发现A表有任何操作,B表也执行同样的操作