CODEWITHSUNDEEP
sqlsql-servert-sqltriggerssql-server-2012
ProfK
ProfK

Reputation: 51064

Why does my trigger to avoid duplicate rows not work?

Before anyone suggests a unique index or key, I have a very good case for this.

I am using this trigger, from Trigger to prevent Insertion for duplicate data of two columns:

CREATE TRIGGER LogDuplicates ON bkPersonPoints
FOR INSERT
AS
if exists (select * from bkPersonPoints c 
                    inner join inserted i 
                        on c.Name = i.Name and c.Points = i.Points)
begin
    rollback
end
GO

That answer is accepted and has 15 up-votes, so I would expect it to work, yet even on my very first insert, into an empty table:

insert bkPersonPoints (Name, Points) values ('Brady', 100)

I get the error:

The transaction ended in the trigger. The batch has been aborted.

APPENDIX: The table looks like this:

CREATE TABLE [dbo].[bkPersonPoints](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Points] [int] NOT NULL
) ON [APP_BR2_User]

Upvotes: 0

Views: 1373

Answers (3)

Aziz Hani
Aziz Hani

Reputation: 11

This answer was inspired by one posted on Apr 13 '20 at 18:34 in Trigger to prevent Insertion for duplicate data of two columns.

CREATE TRIGGER MyTrigger ON dbo.MyTable
INSTEAD OF INSERT
AS

if not exists (
select * from MyTable t
inner join inserted i
on i.name=t.name and i.date=t.date and i.id <> t.id )
begin
Insert into MyTable (Name, Date) Select Name, Date from inserted
end
else
THROW 51000, 'Statement terminated because a duplicate was found for the object', 1;
go

Upvotes: 0

Dmitry Cat
Dmitry Cat

Reputation: 475

Anyway, suggest CONSTRAINT

ALTER TABLE bkPersonPoints
  ADD CONSTRAINT c_uniq_Name_and_points UNIQUE (Name, Points)

Upvotes: 0

Siyual
Siyual

Reputation: 16917

This is happening because it is detecting the record you're currently inserting to the table. You need to filter that out of the EXISTS clause:

CREATE TRIGGER LogDuplicates ON bkPersonPoints
FOR INSERT
AS
if exists (select * from bkPersonPoints c 
                    inner join inserted i 
                        on c.Name = i.Name 
                        and c.Points = i.Points
                        and c.id <> i.id)
begin
    rollback
end
GO

Upvotes: 4

Related Questions