CREATE TABLE [NewProduct] (
[FactoryID] [nvarchar] (50) primary key,
[CustomerID] [nvarchar] (50) ,
[Comment] [nvarchar] (255),
)
GOCREATE TABLE [OldProduct] (
[FactoryID] [nvarchar] (50) primary key,
[CustomerID] [nvarchar] (50) ,
[Comment] [nvarchar] (255),
)
GOinsert newproduct values('95588','ICBC','nothing')
insert OldProduct values('95599','ABC','nothing')现在要把两个表关联,实现 新插入的FactoryID在两个表中都是唯一的)
例如:不允许以下语句执行?
insert newproduct values('95599','ABC','nothing')谢谢
[FactoryID] [nvarchar] (50) primary key,
[CustomerID] [nvarchar] (50) ,
[Comment] [nvarchar] (255),
)
GOCREATE TABLE [OldProduct] (
[FactoryID] [nvarchar] (50) primary key,
[CustomerID] [nvarchar] (50) ,
[Comment] [nvarchar] (255),
)
GOinsert newproduct values('95588','ICBC','nothing')
insert OldProduct values('95599','ABC','nothing')现在要把两个表关联,实现 新插入的FactoryID在两个表中都是唯一的)
例如:不允许以下语句执行?
insert newproduct values('95599','ABC','nothing')谢谢
on NewProduct
instead of insert
as
if not exists (select * from newproduct a where exists(select 1 from inserted where FactoryID=a.FactoryID)) and exists(select * from OldProduct b where exists(select 1 from inserted where FactoryID=b.FactoryID))
begin
insert newproduct select * from inserted
insert OldProduct select * from inserted
end
insert newproduct values( '3 ', 'ABC ', 'nothing ')
提示(所影响的行数为 1 行)
但是表中的数据没有增加