T-SQL - Insert trigger; IF EXISTS not evaluating as intended

Not sure what I'm missing. When I debug and step through the INSERT query I've included below, I see that '%a%' is the value of @Answer, and 103 is the value for @ItemId.

INSERT

'%a%'

@Answer

@ItemId

IF EXISTS is always evaluating to false when I insert the values shown beneath:

IF EXISTS

CREATE TRIGGER TR_cc_Additional_Information_Answers_INS
ON cc_Additional_Information_Answers
AFTER INSERT
AS
BEGIN
CREATE TABLE temp_answers
(
TempAnswer VARCHAR(50),
TempAdditional_Information_ItemID INT
)

INSERT INTO temp_answers (TempAnswer, TempAdditional_Information_ItemID)
SELECT Description, Additional_Information_ItemID
FROM inserted

DECLARE @Answer varchar(50)
SELECT @Answer = '''%' + t.TempAnswer + '%''' FROM temp_answers t

DECLARE @ItemId int
SELECT @ItemId = t.TempAdditional_Information_ItemID FROM temp_answers t

IF EXISTS(SELECT 1
FROM cc_Additional_Information_Answers a
WHERE a.Description LIKE @Answer
AND a.Additional_Information_ItemID = @ItemId)
BEGIN
RAISERROR('Answer is too similar to pre-existing answers for this item', 16, 1)
ROLLBACK TRANSACTION
RETURN
END

DROP TABLE temp_answers
END
GO


And this is my insert query:

INSERT INTO cc_Additional_Information_Answers (Additional_Information_ItemID, Description)
VALUES (103, 'a')


And the pre-existing record:

Thanks in advance, SQL community!

EDIT: this also does not behave as expected. . .

INSERT INTO cc_Additional_Information_Answers (Additional_Information_ItemID, Description)
VALUES (103, 'a')


Given this data

1 Answer
1

Your IF EXISTS will always evaluate to true because the inserted value is already inserted (although it can be rolled back) when the trigger runs (it's an "AFTER" trigger).
So you will want to inspect only those records that existed in the table before the insertion. I always use an outer join for this. Also: I would never create a table in a trigger. The following should work as expected:

CREATE TRIGGER TR_cc_Additional_Information_Answers_INS ON cc_Additional_Information_Answers
AFTER INSERT
AS
BEGIN

IF EXISTS(
SELECT 1 FROM cc_Additional_Information_Answers a
LEFT OUTER JOIN inserted i ON a.Additional_Information_AnswerID = i.Additional_Information_AnswerID
INNER JOIN inserted temp ON a.Additional_Information_ItemID = temp.Additional_Information_ItemID
WHERE a.Description LIKE '%' + temp.Description + '%'
AND i.Additional_Information_AnswerID IS NULL
)
BEGIN
RAISERROR('Answer is too similar to pre-existing answers for this item', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END
GO


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.