有两个数据库 库A 库B
------------------------ ----------------------------------
表man 表depa 表man 表depa
---------- ------------ --------------- ------------
字段 a 字段 a 字段 a 字段 a
字段 b 字段 b 字段 b 字段 b
字段 c 字段 c 字段 c 字段 c
字段 d 字段 d库B与库A相同只是两个表都多了几个字段.问题:
对库A的表man 和表depa 进行(删除\插入\修改)数据时, 库B的表man 和表depa也相应的对数据进行(删除\插入\修改),并且字段d的值赋为1,从而保持两个数据库的数据一致.
希望各位高手帮忙,给小弟个学习的机会!
------------------------ ----------------------------------
表man 表depa 表man 表depa
---------- ------------ --------------- ------------
字段 a 字段 a 字段 a 字段 a
字段 b 字段 b 字段 b 字段 b
字段 c 字段 c 字段 c 字段 c
字段 d 字段 d库B与库A相同只是两个表都多了几个字段.问题:
对库A的表man 和表depa 进行(删除\插入\修改)数据时, 库B的表man 和表depa也相应的对数据进行(删除\插入\修改),并且字段d的值赋为1,从而保持两个数据库的数据一致.
希望各位高手帮忙,给小弟个学习的机会!
create database B
go
use B
go
create table man(a int, b int, c int, d int)
go--
create database A
go
use A
go
create table man(a int, b int, c int)create trigger tr_man_delete on man
for delete
as
delete tmpB
from deleted A, B.dbo.man tmpB
where A.a=tmpB.a and A.b=tmpB.b and A.c=tmpB.c
gocreate trigger tr_man_insert on man
for insert
as
insert B.dbo.man
select *, 1 from inserted
gocreate trigger tr_man_update on man
for update
as
update B.dbo.man tmpB set tmpB.a=inserted.a, tmpB.b=inserted.b, tmpB.c=inserted.c
from inserted, deleted
where tmpB.a=deleted.a and tmpB.b=deleted.b and tmpB.c=deleted.c
go
create trigger tr_man_delete on man
for delete
as
delete tmpB
from deleted A, B.dbo.man tmpB
where A.a=tmpB.a and A.b=tmpB.b and A.c=tmpB.c
gocreate trigger tr_man_insert on man
for insert
as
insert B.dbo.man
select *, 1 from inserted
gocreate trigger tr_man_update on man
for update
as
update B.dbo.man tmpB set tmpB.a=inserted.a, tmpB.b=inserted.b, tmpB.c=inserted.c
from inserted, deleted
where tmpB.a=deleted.a and tmpB.b=deleted.b and tmpB.c=deleted.c
go
------------------------------------------------------------------------------------
但是提示:
服务器: 消息 170,级别 15,状态 1,过程 tr_man_update,行 5
第 5 行: 'tmpB' 附近有语法错误。
是我操作有误么,还是少了点什么,确实不太懂触发器!还望指教!!
for update
as
update tmpB set tmpB.a=inserted.a, tmpB.b=inserted.b, tmpB.c=inserted.c
from B.dbo.man AS tmpB, inserted, deleted
where tmpB.a=deleted.a and tmpB.b=deleted.b and tmpB.c=deleted.c
go