CODEWITHSUNDEEP
sqlsql-servertriggers
GuaGua0308
GuaGua0308

Reputation: 131

Trigger to avoid similar names inserted in tables isnt working and not let me insert any row

I have this trigger to avoid similar names inserted in a table. But isn't working at all and not let me insert any row (as if the condition is true everytime). I tried with IF EXISTS and happen the same. Even deleting everything in the "Actor" table (leaving with no rows) and when I insert a new one the trigger fires.

CREATE TRIGGER [dbo].[AvoidSimilarName] 
ON [dbo].[Actor] 
FOR INSERT
AS 
BEGIN
DECLARE @Firstname varchar(25)
DECLARE @Lastname varchar(25)
SELECT @Firstname = Firstname, @Lastname = Lastname FROM INSERTED;

SELECT * FROM Actor WHERE @Firstname = Firstname AND @Lastname = Lastname 

IF @@ROWCOUNT > 0
BEGIN
ROLLBACK TRANSACTION
END
END

The thing is that when I made a same example of the select that its in the trigger, works fine. Return no rows.

DECLARE @Firstname varchar(25)
DECLARE @Lastname varchar(25)
SET @Firstname = 'Johnny'
SET @Lastname = 'Depp'
SELECT * FROM Actor WHERE @Firstname = Firstname AND @Lastname = Lastname

What am I doing wrong?

Upvotes: 0

Views: 177

Answers (3)

Ed T
Ed T

Reputation: 420

While I agree with @mxix and @Vinnie that you should probably use a unique constraint for this purpose, another option is to use an INSTEAD OF trigger. As others have mentioned, your issue right now is that the data has already been inserted so your search is always finding a match, namely the row that was just inserted. You can use the INSTEAD OF trigger to search for the values about to be inserted, and decide if you want to insert them or not.

Note that if you go this route, you are responsible for inserting into the table if you deem the data should indeed be inserted. See this answer for some more discussion on emulating a before insert trigger.

Also, as @Sean and @marc_s mentioned, your trigger does not correctly handle multiple rows being inserted in one statement.

Upvotes: 0

mxix
mxix

Reputation: 3659

Not trying to help you with the trigger because i don't think is your best option.

Why not create a unique constraint on those 2 fields?

ALTER TABLE dbo.Actor ADD CONSTRAINT UQ_ACTOR_NAME UNIQUE (firstname,lastname)

Upvotes: 3

Clay Sills
Clay Sills

Reputation: 235

I think your trigger always fires because, since you've inserted, you're always going to get a @@rowcount of at least 1. Try setting it to > 1 and see.

Upvotes: 0

Related Questions