Check if the value from one table exists in another tables in SQL Server

Question

I have one table (tbl1) with column ID, the values can be duplicated. I have others tables (tbl2, tbl3...) with column ID , values are unique. I want to write a trigger on insert row in tbl1 and check if ID in new row has not exists in tbl2,tbl3.... There is part of my code.

 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

I don't know how to check in a range of tables, they are stored in table JoinTables(f.ex.). I suppose I need function here.

Table JoinTables similar to

obID|firsttable|secondtable
 1 | tbl1 | tbl2 
 2 | tbl1 | tbl3
 3 | tbl1 | tbl4
 4 | tblM | tbl5
 5 | tblM | tbl6

• Lora: A range of tables is in another table, for example JoinTables, and their names can be changed. and trigger should take actual table names.
• Lora: I need check, if value 'ID' of the insered row exists in related tables

Answer 1

I think you could get it with a single statement.

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<>fiddle here

• Lora: @McNets the number of related tables can be changed, so I need dynamic query. I understand, what should I do, thank you

Answer 2

First of all, your requirement is best suited for Instead of Trigger.

Trigger is best suited for your Requirement than Function.

Sample data of 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')

How many record can each firsttable like tbl1 have ? 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

Then similarly create trigger for table tblM.

If you want to write same logic in UDF or Procedure then in place of inserted table you can use main table name like tbl1, tblM .

If inserted table contain less rows like 5,10 or even 20 then it will perform ok.

You can tell number of rows in each table and whether Id in each table is Clustered Index or not.

This Script can also be use to Dynamically get TableName and Join them.

• Lora: @KurmarHarsh, idea is clear, thank you. Instead of trigger is really more suitable for me
• KumarHarsh: @Lora, check my edited script with minor testing. It work fine.

Answer 3

Here you go, is this what you meant? This should generate a dynamic SQL command for you with all the tables in "JoinTables" (I used McNets's answer as basis):

/*
-- initialization:
IF OBJECT_ID('JoinTables') IS NOT NULL DROP TABLE JoinTables;
CREATE TABLE JoinTables
(
 TableName SYSNAME
);
INSERT INTO JoinTables VALUES ('Table1'),('Table2'),('Table3')
*/
CREATE TRIGGER t1_insert ON T1
AFTER INSERT
AS
BEGIN
 SET NOCOUNT, ARITHABORT, XACT_ABORT ON;
 DECLARE @CMD NVARCHAR(MAX), @Exists BIT
 SET @Exists = 0
 SELECT @CMD = ISNULL(@CMD + N'
 UNION ALL
 ', N' ') + N'SELECT ID FROM ' + TableName
 FROM JoinTables
 SET @CMD = N'SELECT TOP 1 @Exists = 1
 FROM inserted INNER JOIN (
 ' + @CMD + N') AS others 
 ON others.ID = inserted.ID'
 --PRINT @CMD
 EXEC sp_executesql @CMD, N'@Exists BIT OUTPUT', @Exists = @Exists OUTPUT
 IF @Exists = 1
 BEGIN
 PRINT 'YES';
 END
 ELSE
 BEGIN
 PRINT 'NO';
 END
END

EDIT: As noted by others, the INSERTED and DELETED tables will not be accessible within the dynamic SQL context, so their contents would first need to be copied to temporary tables (e.g. #inserted and #deleted) in the main trigger code, and those should be the tables referenced by the dynamic SQL.

• Jonathan Fite: Is the INSERTED virtual table accessible in the context of the sp_executesql? Have you tested that?
• Lora: The virtual table is not accessible, but I modified as @HumarHash adviced, I put Inserted table in temp one