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 或是处理过程
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 或是处理过程
第一步:添加A表有B表中没有记录
insert b(id,name) select id,name from a where not exists(select id from b where b.id=a.id)
第一步:删除A表没有B表中有记录
delete t from b t where not exists(select 1 from a where id=t.id)
第一步:添加A表有B表中没有记录
insert b(id,name) select id,name from a where not exists(select id from b where b.id=a.id)
第一步:删除A表没有B表中有记录
delete t from b t where not exists(select 1 from a where id=t.id)
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=nameA表数据删除,B表数据删除
delete from B where id=id and name=name
delete from A where id=id and name=name你总有办法判断用户会选择添加,更新和删除操作吧
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
goinsert 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')
删:Delete From B Where Id='旧Id值'
改:Update B Set [Name]='name值' Where Id='Id值'