表A
SheBeiID type
00001 12
00001 13表B
SheBeiID type value
00001 12 78.08比如现在有三份数据,00001 12 90、00001 13 70、00001 14 78.02
那么应该是最后一份不被保存,因为type=14的不存在表A里,属于不合法数据,请问这样的要求该如何写SQL?
SheBeiID type
00001 12
00001 13表B
SheBeiID type value
00001 12 78.08比如现在有三份数据,00001 12 90、00001 13 70、00001 14 78.02
那么应该是最后一份不被保存,因为type=14的不存在表A里,属于不合法数据,请问这样的要求该如何写SQL?
begin
/*你的语句*/end
Instead Of Insert
As
Insert B Select C.* From A Inner Join Inserted C On A.SheBeiID = C.SheBeiID And A.type = C.type
GO
Create Table A
(SheBeiID Char(5),
type Int)
Insert A Select '00001', 12
Union All Select '00001', 13Create Table B
(SheBeiID Char(5),
type Int,
value Decimal(10, 2))
GO
--創建觸發器
Create Trigger TR_Insert_B On B
Instead Of Insert
As
Insert B Select C.* From A Inner Join Inserted C On A.SheBeiID = C.SheBeiID And A.type = C.type
GO
--測試
Insert B Select '00001', 12, 90
Union All Select '00001', 13, 70
Union All Select '00001', 14, 78.02Select * From B
GO
--刪除測試環境
Drop Table A, B
--結果
/*
SheBeiID type value
00001 12 90.00
00001 13 70.00
*/
select * from tb1 where type in(select type from tb2)