If Exists in trigger

Question

Is there a way I can check if a value exists in a table I want to insert into activated by a trigger? If the value does exist, I want nothing to be done and if it doesn't I would like it to be inserted.

This is my current trigger

ALTER TRIGGER [dbo].[Update]
On [dbo].[A] 
AFTER UPDATE
AS
Declare @Id int;
SELECT @Id = Issue FROM Inserted


INSERT INTO dbo.[B] (id, problem)
    SELECT BugId, ProjectID
    FROM dbo.[C]
    WHERE BugId = @Id and (projectid = 547)

Many thanks

possible duplicate of [SQL Server: trigger to fire only if a condition is met](http://stackoverflow.com/questions/280904/sql-server-trigger-to-fire-only-if-a-condition-is-met) — Johann Blais, Jun 08 '11 at 09:00
@Johann - and the answer to that question is still faulty in the presence of multiple rows in the `inserted` table, some of which match the condition, some of which don't. — Damien_The_Unbeliever, Jun 08 '11 at 09:32

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

You should do it as described in this SO post. Use an IF statement to check the existence.

edited May 23 '17 at 10:34

Community

1
1

answered Jun 08 '11 at 08:46

deltaforce2

583
2
8

The problem with an `IF` test like that is it provides a single true/false answer for potentially multiple rows in the `inserted` pseudo-table - there may actually be a mix of true/false outcomes in there. – Damien_The_Unbeliever Jun 08 '11 at 09:00

score 1 · Accepted Answer · answered Jun 08 '11 at 08:51

inserted can contain multiple rows. And left join can be your friend for testing for whether rows already exist:

ALTER TRIGGER [dbo].[Update]
On [dbo].[A] 
AFTER UPDATE
AS

INSERT INTO dbo.[B] (id, problem)
    SELECT BugId, ProjectID
    FROM
       dbo.[C]
          inner join
       inserted i
          on
              c.BugID = i.Issue
          left join
       dbo.B
           on
               B.ID = c.BugID
    WHERE
        C.projectid = 547 and B.BugID is null

If Exists in trigger

2 Answers2