w1增加记录同时也插入到W2表呀 CREATE TRIGGER TriggerW1 ON w1 INSTEAD OF INSERT AS BEGING INSERT INTO W2 SELECT aa,bb from inserted END GO
--来自邹建--用触发器即时同步两个表的实例:--测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test--创建测试表,不能用标识列做主键,因为不能进行正常更新 --在远程主机上建表 if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [test]create table test(id int not null constraint PK_test primary key ,name varchar(10)) go--以下操作在本机进行 --在本机上建表及做同步处理的工作 if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [test]create table test(id int identity(1,1) primary key ,name varchar(10)) go--创建同步的触发器 create trigger t_test on test for insert,update,delete as set XACT_ABORT on --启动远程服务器的MSDTC服务 exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output--启动本机的MSDTC服务 exec master..xp_cmdshell 'net start msdtc',no_output--进行分布事务处理,如果表用标识列做主键,用下面的方法 BEGIN DISTRIBUTED TRANSACTION delete from openrowset('sqloledb','xz';'sa';'',test.dbo.test) where id in(select id from deleted) insert into openrowset('sqloledb','xz';'sa';'',test.dbo.test) select * from inserted commit tran go--插入数据测试 insert into test select 1,'aa' union all select 2,'bb' union all select 3,'c' union all select 4,'dd' union all select 5,'ab' union all select 6,'bc' union all select 7,'ddd'--删除数据测试 delete from test where id in(1,4,6)--更新数据测试 update test set name=name+'_123' where id in(3,5)--显示测试的结果 select * from test a full join openrowset('sqloledb','xz';'sa';'',test.dbo.test) b on a.id=b.id--无主键时: if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [test]create table test(name varchar(10)) go--以下操作在本机进行 --在本机上建表及做同步处理的工作 if exists (select * from dbo.sysobjects where id = object_id(N'[test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table test1create table test1( name varchar(10)) go--创建同步的触发器 create trigger t_test on test for insert,update,delete as BEGIN DISTRIBUTED TRANSACTION delete from test1 where name in(select name from deleted) insert into test1 select * from inserted commit tran goinsert test select '100' union all select '200'delete test where name='100'update test set name='300' where name='200'select * from test select * from test1drop table test drop table test1
--增加触发器 if exists(select * from tu_w1_insert) drop trigger tu_w1_insert go create trigger tu_w1_insert on w1 for insert as begin insert into w2(aa,bb) select a.aa,a.bb from w1 a where a.aa in (select b.aa from inserted b) end --删除触发器 if exists(select * from tu_w1_delete) drop trigger tu_w1_delete gocreate trigger tu_w1_delete on w1 for delete as begin delete from w2 where aa in (select aa from deleted) end
CREATE TRIGGER TriggerW1 ON w1
INSTEAD OF INSERT
AS
BEGING
INSERT INTO W2
SELECT aa,bb from inserted
END
GO
--在远程主机上建表
if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [test]create table test(id int not null constraint PK_test primary key
,name varchar(10))
go--以下操作在本机进行
--在本机上建表及做同步处理的工作
if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [test]create table test(id int identity(1,1) primary key
,name varchar(10))
go--创建同步的触发器
create trigger t_test on test
for insert,update,delete
as
set XACT_ABORT on
--启动远程服务器的MSDTC服务
exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output--启动本机的MSDTC服务
exec master..xp_cmdshell 'net start msdtc',no_output--进行分布事务处理,如果表用标识列做主键,用下面的方法
BEGIN DISTRIBUTED TRANSACTION
delete from openrowset('sqloledb','xz';'sa';'',test.dbo.test)
where id in(select id from deleted)
insert into openrowset('sqloledb','xz';'sa';'',test.dbo.test)
select * from inserted
commit tran
go--插入数据测试
insert into test
select 1,'aa'
union all select 2,'bb'
union all select 3,'c'
union all select 4,'dd'
union all select 5,'ab'
union all select 6,'bc'
union all select 7,'ddd'--删除数据测试
delete from test where id in(1,4,6)--更新数据测试
update test set name=name+'_123' where id in(3,5)--显示测试的结果
select * from test a full join
openrowset('sqloledb','xz';'sa';'',test.dbo.test) b on a.id=b.id--无主键时:
if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [test]create table test(name varchar(10))
go--以下操作在本机进行
--在本机上建表及做同步处理的工作
if exists (select * from dbo.sysobjects where id = object_id(N'[test1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table test1create table test1(
name varchar(10))
go--创建同步的触发器
create trigger t_test on test
for insert,update,delete
as
BEGIN DISTRIBUTED TRANSACTION
delete from test1
where name in(select name from deleted)
insert into test1
select * from inserted
commit tran
goinsert test
select '100'
union all select '200'delete test
where name='100'update test
set name='300'
where name='200'select * from test
select * from test1drop table test
drop table test1
if exists(select * from tu_w1_insert)
drop trigger tu_w1_insert
go
create trigger tu_w1_insert
on w1
for insert
as
begin
insert into w2(aa,bb)
select a.aa,a.bb from w1 a
where a.aa in (select b.aa from inserted b)
end
--删除触发器
if exists(select * from tu_w1_delete)
drop trigger tu_w1_delete
gocreate trigger tu_w1_delete
on w1
for delete
as
begin
delete from w2
where aa in (select aa from deleted)
end