异地 定时 同步 部分 数据 的事务 存储过程 以及触发器

use dings
truncate   table dingsai ------------删除表
use ding

drop trigger insert_dingsai --删除触发

drop procedure ds_updatedingsai ----删除存储过程

{call procAllSynchronization} -------------------执行总存储过程

{call ds_insertdingsai}------------------------执行同步部分插入

{call ds_updatedingsai}------------------------执行同步部分更新

insert into dingsai (username,password)values('132','132')                 -----TEST Insert

update dingsai set username='33',password='33' where id=1        -----TEST Update   

use dings
select * from dingsai                      --------查询
use ding

insertdingsai   updatedingsai   copydingsai    dingsai                  ------ 几个表名

insert_dingsai    update_dingsai                                 -------几个触发器名      

ds_insertdingsai      ds_updatedingsai   procAllSynchronization -----几个存储过程名

----------------------------Test--------------------------------------------------------------------

create trigger insert_dingsai 
on dingsai 
for insert 
as 
begin 
   declare   @name   varchar(50) 
select @name= convert(varchar(50),id ) from inserted 
insert into insertdingsai(name) values (@name) 
end

create trigger update_dingsai 
on dingsai 
for update 
as 
begin 
insert into updatedingsai(name)select convert(varchar(50),id )from inserted 
end

create procedure ds_insertdingsai 
as 
begin

declare @i int 
declare @j int 
exec sp_addlinkedsrvlogin 
@rmtsrvname = '192.168.1.102',
@useself='False', 
@rmtuser = 'sa', 
@rmtpassword ='sa'

BEGIN TRANSACTION-----------------------------开始事务

DECLARE @errors INT ------------------------------定义错误计数器
SET @errors=0 ------------------------------------没错为0

set @i=1 
select @j=count(name)+1 from insertdingsai 
while @i< @j 
begin 
   insert into 
DINGS.dbo.dingsai
(username,password) 
select 
username,password 
from dingsai 
where 
convert(varchar(50),id)=(select name from insertdingsai where )
set @i=@i+1
end 
truncate table insertdingsai

set @errors=@errors+@@error---------------------------------------/
IF @errors<>0
BEGIN
PRINT '有错误，回滚'
ROLLBACK TRANSACTION--事务回滚语句
END
ELSE
BEGIN
PRINT '成功，提交'
COMMIT TRANSACTION--事务提交语句
end -------------------------------------------------------/

end

create procedure ds_updatedingsai 
as 
begin 
exec sp_addlinkedsrvlogin 
@rmtsrvname = '192.168.1.102', 
@useself='False', 
@rmtuser = 'sa', 
@rmtpassword ='sa' 
declare @username varchar(50) 
declare @password varchar(50) 
declare @i int 
declare @j int

BEGIN TRANSACTION-----------------------------开始事务

DECLARE @errors INT ------------------------------定义错误计数器
SET @errors=0 ------------------------------------没错为0

set @i=1 
select @j=count(name)+1 from updatedingsai 
while @i< @j 
begin select @username=username, @password=password 
from dingsai where convert(varchar(50),id)=(select name from updatedingsai where ) 
update DINGS.dbo.dingsai set 
,    
where convert(varchar(50),id)=(select name from updatedingsai where ) 
set @i=@i+1 
end 
truncate table updatedingsai

set @errors=@errors+@@error---------------------------------------/
IF @errors<>0
BEGIN
PRINT '有错误，回滚'
ROLLBACK TRANSACTION--事务回滚语句
END
ELSE
BEGIN
PRINT '成功，提交'
COMMIT TRANSACTION--事务提交语句
end -------------------------------------------------------/

end

-------------------------------------------------------------

create procedure procAllSynchronization
as 
begin 
{call ds_insertdingsai}

{call ds_updatedingsai}

end

---------------------------------------------------------------

#数据库