0

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
  • 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 Jul 31 '19 at 10:30
  • I need check, if value 'ID' of the insered row exists in related tables – Lora Jul 31 '19 at 11:34
2

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

| improve this answer | |
  • @McNets the number of related tables can be changed, so I need dynamic query. I understand, what should I do, thank you – Lora Jul 31 '19 at 14:17
0

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.

| improve this answer | |
  • @KurmarHarsh, idea is clear, thank you. Instead of trigger is really more suitable for me – Lora Aug 1 '19 at 8:46
  • @Lora, check my edited script with minor testing. It work fine. – KumarHarsh Aug 1 '19 at 10:55
0

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.

| improve this answer | |
  • Is the INSERTED virtual table accessible in the context of the sp_executesql? Have you tested that? – Jonathan Fite Jul 31 '19 at 13:51
  • 1
    The virtual table is not accessible, but I modified as @HumarHash adviced, I put Inserted table in temp one – Lora Aug 1 '19 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.