CODEWITHSUNDEEP
sql-servertriggersexists
Node17
Node17

Reputation: 537

If Exists in trigger

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

Upvotes: 0

Views: 3796

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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

Upvotes: 1

deltaforce2
deltaforce2

Reputation: 593

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

Upvotes: 1

Related Questions