两表记录同步怎么办
A表
id name
1 n1
2 n2
3 n3
由A表产生B表
id name data
1 n1
2 n2
3 n3
然后为B表的 data 手工录入数据：
id name data
1 n1 d1
2 n2 d2
3 n3 d3
当
A表改变为
id name
1 n1
3 n3
4 n4
B表变为
id name data
1 n1 d1
3 n3 d3
4 n4
意思为A、B两表 id name 同步 ，但要保留 data字段输入的数据
求 sql 或是处理过程
[解决办法]
分3种情况嘛
A表数据新增，B表也同时新增
insert into A(id,name) values(id,name)
insert into B(id,name) values(id,name)
A表数据更新，B表也同时更新
update B set id=new_id,name=new_name where id=id and name=name
update A set id=new_id,name=new_name where id=id and name=name
A表数据删除，B表数据删除
delete from B where id=id and name=name
delete from A where id=id and name=name
你总有办法判断用户会选择添加，更新和删除操作吧
[解决办法]
3楼已经给你答案了。
我假设a,b两个表，
a:
aid,aname aid为主键
b:
bid,bname,bdata bid为主键
然后……
给A表添加触发器，代码如下：
CREATE TRIGGER tr_test
ON a
FOR INSERT,DELETE
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT * FROM inserted)
INSERT INTO b SELECT aname, '' FROM inserted
ELSE
DELETE FROM b WHERE EXISTS (SELECT * FROM deleted WHERE bname = aname)
END
GO
测试语句：
use test
go
insert into a values ('n1')
insert into a values ('n2')
insert into a values ('n3')
update b set bdata = 'd1' where bname = 'n1'
update b set bdata = 'd2' where bname = 'n2'
update b set bdata = 'd3' where bname = 'n3'
delete from a where aname = 'n1'
insert into a values ('n4')
[解决办法]
增:Insert Into B(Id,[name]) Values('Id新值','name值')
删:Delete From B Where Id='旧Id值'
改:Update B Set [Name]='name值' Where Id='Id值'
