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
---------------------------------------------------------------