CODEWITHSUNDEEP
sql-serverdatabasetriggersexists
James
James

Reputation: 2246

Check data existance before inserting to table

I have a trigger which inserts data to a table from another table based on a condition like below :

DECLARE @pID VARCHAR(50);

SELECT @pID = I.pID FROM inserted I;

INSERT  INTO Queue ( ID )
        SELECT  ID
        FROM    Inventory
        WHERE   pID = @pID

How can I check if the ID exist before inserting?

Note : There will be multiple records returned from - 

SELECT  ID
            FROM    Inventory
            WHERE   pID = @pID

Upvotes: 1

Views: 2186

Answers (1)

deroby
deroby

Reputation: 6002

As you already mention, [inserted] can contain zero, one or multiple records. The approach to store that info into a variable will NOT work.

Anyway, I'm assuming that you want to check for existence to avoid the same ID is not entered in the Queue table multiple times ?

In that case you can use the following code in your trigger:

INSERT  INTO Queue ( ID )
        SELECT  DISTINCT I.ID -- as to avoid same ID being inserted multiple times
        FROM    Inventory I
        JOIN    inserted
          ON    inserted.pID = I.pID
        WHERE   NOT EXISTS ( SELECT *
                               FROM Queue Q
                              WHERE Q.ID = I.ID )

Upvotes: 1

Related Questions