Why does my trigger to avoid duplicate rows not work?

Question

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]

Scott's comment on that answer contains a key piece of information - you need to ensure that the rows in `inserted` **don't** match *themselves* in the table (since this is a `for`/`after` trigger, the inserted rows are already in the table) — Damien_The_Unbeliever, May 05 '17 at 13:56

score 4 · Accepted Answer · answered May 05 '17 at 13:59

4

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

answered May 05 '17 at 13:59

Siyual

16,415
8
44
58

Yes, thanks. I did have some doubt about the record already existing AFTER INSERT but in my clamoring for an answer the PK idea eluded me. – ProfK May 05 '17 at 14:16

score 0 · Answer 2 · answered May 05 '17 at 14:09

0

Anyway, suggest CONSTRAINT

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

answered May 05 '17 at 14:09

Dmitry Cat

475
3
11

2

**Literally** the first line of the question starts "Before anyone suggests a unique index or key..." – Damien_The_Unbeliever May 05 '17 at 14:11
Better be a darn good case to not use the specific database feature designed to handle this exact situation... – Jacob H May 05 '17 at 14:13
@Damien_The_Unbeliever yes, but i guess, that there is no point to use trigger for check uniqueness, so i've suggested :3 – Dmitry Cat May 05 '17 at 14:13
My use case is top not necessarily merely prevent the record being inserted, but to log the potential duplicate and hopefully something about what caused the duplicate insert. I may even need to not prevent the insert, but just zero out the amount on a duplicate. A unique key is useless for any of that except preventing the insert. – ProfK May 05 '17 at 14:34

score 0 · Answer 3 · answered May 31 '21 at 14:39

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

Why does my trigger to avoid duplicate rows not work?

3 Answers3