检索后
T1 表如下:
T1Code Flag Name
T1001 0 T1NAME1
T1002 0 T1NAME2T2 如下:
T2Code Flag Name
T2001 1 T2NAME1
T2002 1 T2NAME2请问如何将T1和T2拼到一起如下结果: T 如下:
TCode Flag Name
T1001 0 T1NAME1
T1002 0 T1NAME2
T2001 1 T2NAME1
T2002 1 T2NAME2
谢谢!
在DataGird中绑定的是表T , 这里需要考虑更新表T时, T1 T2也要更新
如在表T中删除T2001 那么T2中也要删除T2002 如果添加一条记录T1003 那么表T1会增加一条记录T1003注:这里的Flag 是我自己加的一个逻辑字段,以便判断记录属于哪张表。如果可以实现上述功能,这个Flag字段可以不要。
T1 表如下:
T1Code Flag Name
T1001 0 T1NAME1
T1002 0 T1NAME2T2 如下:
T2Code Flag Name
T2001 1 T2NAME1
T2002 1 T2NAME2请问如何将T1和T2拼到一起如下结果: T 如下:
TCode Flag Name
T1001 0 T1NAME1
T1002 0 T1NAME2
T2001 1 T2NAME1
T2002 1 T2NAME2
谢谢!
在DataGird中绑定的是表T , 这里需要考虑更新表T时, T1 T2也要更新
如在表T中删除T2001 那么T2中也要删除T2002 如果添加一条记录T1003 那么表T1会增加一条记录T1003注:这里的Flag 是我自己加的一个逻辑字段,以便判断记录属于哪张表。如果可以实现上述功能,这个Flag字段可以不要。
union all
select * from t2
as
select * from t1
union all
select * from t2
go
--> -->
if not object_id('T1') is null
drop table T1
Go
Create table T1([T1Code] nvarchar(5),[Flag] int,[Name] nvarchar(7))
Insert T1
select N'T1001',0,N'T1NAME1' union all
select N'T1002',0,N'T1NAME2'
Go
--> -->
if not object_id('T2') is null
drop table T2
Go
Create table T2([Code] nvarchar(5),[Flag] int,[Name] nvarchar(7))
Insert T2
select N'T2001',1,N'T2NAME1' union all
select N'T2002',1,N'T2NAME2'
Go
create view T
as
select * from t1
union all
select * from T2
go
create trigger Tr_T on T
instead of update
as
begin
delete t1 where checksum(*)in(select checksum(*) from deleted )
delete t2 where checksum(*)in(select checksum(*) from deleted )
insert t1 select * from inserted where [Flag]=0
insert t2 select * from inserted where [Flag]=1
endgoupdate t set [Name]='中國風' where [Flag]=0 and [T1Code]=N'T1001'select * from t1
/*
------ ----------- -------
T1001 0 中國風
T1002 0 T1NAME2*/
update t set [Name]='中國風V2' where [Flag]=1 and [T1Code]=N'T2001'select * from t2/*
Code Flag Name
----- ----------- -------
T2001 1 中國風V2
T2002 1 T2NAME2
*/
5楼写的很全面,再稍微补充一下
如果表t1,t2中不设置标识列flag 的话,可以这样修改触发器
create trigger Tr_T on T
instead of update
as
begin
delete t1 where checksum(*)in(select checksum(*) from deleted )
delete t2 where checksum(*)in(select checksum(*) from deleted )
insert t1 select * from inserted where left([T1Code],2)='T1'
insert t2 select * from inserted where left([T1Code],2)='T2'
end