troubleshooting Question

SQL Trigger preventing certain inserts

Avatar of arthrex
arthrexFlag for Germany asked on
Microsoft SQL Server 2005
2 Comments1 Solution226 ViewsLast Modified:
Hi experts,

I created a trigger which is supposed to prevent duplicate entries into a table. That is, if there is already a record with the same FirstName, Name and Institution the trigger should prohibit the insert.
It's actually not a big deal, but somehow it doesn't do what it should. The trigger fires on any insert right now.
Since I don't know how to debug triggers (some advice here?), could someone please look over my trigger?

Thanks a lot
ALTER TRIGGER [dbo].[InsertSperre]
   ON  [dbo].[Participant]
   FOR INSERT
AS 
DECLARE @myName varchar(50)
DECLARE @myFirstName varchar(50)
DECLARE @myInstitution varchar(50)
 
SELECT @myName = INSERTED.Name, @myFirstName = FirstName,  @myInstitution = Institution FROM INSERTED 
 
IF EXISTS (SELECT * FROM Participant 
			WHERE Participant.Name = @myName AND 
			FirstName = @myFirstName AND
			Institution = @myInstitution)		   
BEGIN
 
	SET NOCOUNT ON;
 
	ROLLBACK TRANSACTION 
 
END
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros