arthrex
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.