檢查一個表中的值是否存在於 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它們。

引用自：https://dba.stackexchange.com/questions/244197