Link to home
Start Free TrialLog in
Avatar of arthrex
arthrexFlag for Germany

asked on

SQL Trigger preventing certain inserts

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

Open in new window

Avatar of mastoo
mastoo
Flag of United States of America image

The inserts have already happened when that trigger fires.  You could change the trigger type, do a select with group by those 3 columns looking for count > 1, or the easiest would be just put a unique constraint on the table for those 3 columns.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial