表述的不是很清楚。举个例子吧,假如是A插入记录,就将A的ID插入到B、C表中Create Trigger [Update_BC] ON A
FOR INSERT
AS
Begin
Insert B(ID) Select ID from Inserted
Insert C(ID) Select ID from Inserted
End
GO得到修改和删除的ID都是Select ID from Deleted
FOR INSERT
AS
Begin
Insert B(ID) Select ID from Inserted
Insert C(ID) Select ID from Inserted
End
GO得到修改和删除的ID都是Select ID from Deleted
--例子:
--测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test
--服务器上的表(查询分析器连接到服务器上创建)
create table [user](id int primary key,number varchar(4),name varchar(10))
go
--以下在局域网(本机操作)
--本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]
GO
create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
go
--创建触发器,维护state字段的值
create trigger t_state on [user]
after update
as
update [user] set state=1
from [user] a join inserted b on a.id=b.id
where a.state is not null
go--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名为:xz,用户名为:sa,无密码
if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
go
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','xz'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa'
go--创建同步处理的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_synchro]
GO
create proc p_synchro
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 srv_lnk.test.dbo.[user]
where id not in(select id from [user]) --同步新增的数据
insert into srv_lnk.test.dbo.[user]
select id,number,name from [user] where state is null
--同步修改的数据
update srv_lnk.test.dbo.[user] set
number=b.number,name=b.name
from srv_lnk.test.dbo.[user] a
join [user] b on a.id=b.id
where b.state=1
--同步后更新本机的标志
update [user] set state=0 where isnull(state,1)=1
--COMMIT TRAN
go--创建作业,定时执行数据同步的存储过程
if exists(SELECT 1 from msdb..sysjobs where name='数据处理')
EXECUTE msdb.dbo.sp_delete_job @job_name='数据处理'
exec msdb..sp_add_job @job_name='数据处理'--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_synchro' --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据同步',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '数据处理',
@name = '时间安排',
@freq_type = 4, --每天
@freq_interval = 1, --每天执行一次
@active_start_time = 00000 --0点执行
go
如何返回a表刚刚插入,修改,删除的记录的ID号?
你可以在程序中实现控制。
as
declare @id int
select @id=id from inserted --获得插入的记录
.....
gocreate trigger tu_a for delete
as
declare @id int
select @id=id from deleted --获得删除的记录
.....
gocreate trigger tu_a for update
as
declare @fld1 varchar(12),@fld2 varchar(12)
select @fld1=fld from inserted --获得更新后的值
select @fld2=fld from deleted --获得更新前的值
....
go.....
go
Create Trigger [Insert_BC] ON A
FOR INSERT
AS
Begin
Insert B(用户名,密码) Select 用户名,密码 from Inserted
Insert C(用户名,密码) Select 用户名,密码 from Inserted
End
GO
--修改的Trigger
Create Trigger [Update_BC] ON A
FOR Update
AS
Begin
Update T1 Set 密码=B.密码 from B T1 Inner Join Inserted T2 On T1.用户名=T2.用户名
Update T1 Set 密码=B.密码 from C T1 Inner Join Inserted T2 On T1.用户名=T2.用户名
End
GO
--删除的Trigger
Create Trigger [Delete_BC] ON A
FOR DELETE
AS
Begin
Delete T1 from B T1 Inner Join Deleted T2 On T1.用户名=T2.用户名
Delete T1 from C T1 Inner Join Deleted T2 On T1.用户名=T2.用户名
End
GO
列前缀 'B' 与查询中所用的表名或别名不匹配Update T1 Set [passowrd]=B.[passowrd] from B T1 Inner Join Inserted T2 On T1.userid=T2.id
Update T1 Set [passowrd]=B.[passowrd] from C T1 Inner Join Inserted T2 On T1.uid=T2.id
不过还是一样,报错的应该是指 B.[password] 这里
表B userid password
表C uid password改变A的pwd,其它两个password也改变Update T1 Set [password]=B.[password] from B T1 Inner Join Inserted T2 On T1.userid=T2.id
Update T1 Set [password]=B.[password] from C T1 Inner Join Inserted T2 On T1.uid=T2.id
Create Trigger [Update_BC] ON A
FOR Update
AS
Begin
Update T1 Set [password]=B.pwd from B T1 Inner Join Inserted T2 On T1.userid=T2.id
Update T1 Set [password]=B.pwd from C T1 Inner Join Inserted T2 On T1.uid=T2.id
End
GO
FOR Update
AS
Begin
Update T1 Set [password]=jgame_user.pwd from jgame_user T1 Inner Join Inserted T2 On T1.userid=T2.id
Update T1 Set [password]=jgame_user.pwd from vuserinfo T1 Inner Join Inserted T2 On T1.uid=T2.id
End
GO-----------------------Create Trigger [Update_GameChat] ON userinfo
FOR Update
AS
Begin
Update T1 Set [password]=jgame_user.pwd from jgame_user T1 Inner Join Inserted T2 On T1.userid=T2.id
Update T1 Set [password]=vuserinfo.pwd from vuserinfo T1 Inner Join Inserted T2 On T1.uid=T2.id
End
GO都不行?`~
Create Trigger [Update_BC] ON userinfo
FOR Update
AS
Begin
Update T1 Set [password]=T2.pwd from jgame_user T1 Inner Join Inserted T2 On T1.userid=T2.id
Update T1 Set [password]=T2.pwd from vuserinfo T1 Inner Join Inserted T2 On T1.uid=T2.id
End
GO