Sql-Server-2012
檢查一個表中的值是否存在於 SQL Server 的另一個表中
我有一個帶有列 ID 的表(tbl1),值可以重複。我有其他表(tbl2,tbl3 …),列 ID ,值是唯一的。我想在 tbl1 中的插入行上編寫一個觸發器,並檢查新行中的 ID 是否在 tbl2、tbl3 中不存在……我的程式碼有一部分。
CREATE TRIGGER dbo.tbl1_ID ON dbo.tbl1 AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @CHECK int SELECT OBJECTID,ID, ROW_NUMBER() over(Order by OBJECTID) as aID into #T1 from inserted where (ID is not null) SELECT @CHECK = COUNT(p.ID from #T1 as p where not exists (select e.ID from dbo.tbl2 as e where p.ID=e.ID)) IF @CHECK>0 BEGIN RAISERROR("ID ALREADY EXISTS",16,1); ROLLBACK TRANSACTION; END END
我不知道如何檢查一系列表,它們儲存在表 JoinTables(f.ex.) 中。我想我需要在這裡發揮作用。
表 JoinTables 類似於
obID|firsttable|secondtable 1 | tbl1 | tbl2 2 | tbl1 | tbl3 3 | tbl1 | tbl4 4 | tblM | tbl5 5 | tblM | tbl6
我想你可以用一個單一的聲明來理解它。
CREATE TRIGGER t1_insert ON T1 AFTER INSERT AS BEGIN IF EXISTS(SELECT 1 FROM inserted JOIN ( SELECT ID FROM T2 UNION ALL SELECT ID FROM T3 UNION ALL SELECT ID FROM T4 ) others ON others.ID = inserted.ID) BEGIN PRINT 'YES'; END ELSE BEGIN PRINT 'NO'; END END
db<>在這裡擺弄
首先,您的要求最適合
Instead of Trigger
.
Trigger
最適合您的要求,而不是Function
.的樣本數據
joinTable
,create table #oinTables(obID int,firsttable varchar(20),secondtable varchar(20)) insert into #oinTables values (1 , 'tbl1' , 'tbl2') , (2 , 'tbl1' , 'tbl3') ,(3 , 'tbl1' , 'tbl4') ,(4 , 'tblM' , 'tbl5') ,( 5 , 'tblM' , 'tbl6')
firsttable
每個像 tbl1可以有多少條記錄?4-5 tables
?Dynamic query can be created in this manner, DECLARE @FirstTable VARCHAR(50)='tbl1' DECLARE @JoinCond varchar(500)='' DECLARE @JoinValue varchar(500)='' DECLARE @Sql nvarchar(4000)=N'' declare @Exists int --Inerted/Deleted Table cannot be use in dynamic Sql --So put inserted column in #temp table create table #Inserted(id int) insert into #Inserted(id) select id from tbl1 ; ;With CTE as ( select * , CONCAT(' left join ',secondtable ,' t',obID,' on ',' t',obID,'.id',' = ','i.id') SqlCol ,CONCAT(' t',obID,'.id') ValueCol from #oinTables where firsttable=@FirstTable ) select top 1 @JoinCond= (select SqlCol+ ' ' from cte for xml path('')) ,@JoinValue= (select ','+ValueCol from cte for xml path('')) from CTE set @JoinValue= STUFF(@JoinValue,1,1,'') -- remove first comma without bug set @JoinValue=CONCAT('coalesce(',@JoinValue,')') set @Sql=concat(' SELECT @Exists='+@JoinValue+' FROM #Inserted i ' ,@JoinCond) set @Sql=' '+ @Sql +' ' print @Sql Exec sp_executesql @Sql ,N'@Exists int OUTPUT' ,@Exists OUTPUT select @Exists if(@Exists is not null ) BEGIN RAISERROR('ID ALREADY EXISTS',16,1); --ROLLBACK TRANSACTION; print 'rollback' END drop table #oinTables,#Inserted
然後類似地為 table 創建觸發器
tblM
。如果要在表中
UDF
或Procedure
代替inserted
表中編寫相同的邏輯,則可以使用主表名稱,例如tbl1, tblM
.如果
inserted
表包含較少的行,如 5,10 甚至 20,那麼它將執行正常。您可以知道每個表中的行數以及每個表中的 Id 是否為
Clustered Index
。這
Script
也可以用於Dynamically
獲取TableName
和Join
它們。