Trigger to prevent Insertion for duplicate data of two columns

Question

I am working on SQL Server 2008R2, I am having the following Table

ID     Name     date
 1     XYZ      2010
 2     ABC      2011
 3     VBL      2010

Now i want to prevent insertion if i have a Data although the ID is different but data is present

 ID    Name     date
  4    ABC      2011

Kindly guide me how should i write this trigger.

Do you really need a trigger? Have you tried unique index or constraint? — Elmer, Aug 29 '13 at 05:46
CREATE UNIQUE NONCLUSTERED INDEX XXTable_Unq ON xxTable (name ASC,[date] ASC) — Elmer, Aug 29 '13 at 05:55
alow you can use this sql to insert: `insert into tb(name,date)select 'ABC',2048 from dual where not exists (select from tb where name='ABC')` — youbl, Jun 01 '21 at 08:05

Rocklan · Accepted Answer · 2017-05-08T04:07:10.230

24

Something like this:

CREATE TRIGGER MyTrigger ON dbo.MyTable
AFTER INSERT
AS

if exists ( select * from table t 
    inner join inserted i on i.name=t.name and i.date=t.date and i.id <> t.id)
begin
    rollback
    RAISERROR ('Duplicate Data', 16, 1);
end
go

That's just for insert, you might want to consider updates too.

Update

A simpler way would be to just create a unique constraint on the table, this will also enforce it for updates too and remove the need for a trigger. Just do:

ALTER TABLE [dbo].[TableName]    
ADD CONSTRAINT [UQ_ID_Name_Date] UNIQUE NONCLUSTERED
(
    [Name], [Date]
)

and then you'll be in business.

edited May 08 '17 at 04:07

answered Aug 29 '13 at 05:22

Rocklan

7,888
3
34
49

although this raise the error but cannot stop insertion, my problem is still there – WiXXeY Aug 29 '13 at 07:19
Oh yep, that's because I missed the ROLLBACK line - so add that one in like I just have. But have a look at James's answer, maybe a unique constraint is what you're after. – Rocklan Aug 30 '13 at 01:39
5

If you need to use the trigger method (because you want a custom error message, for example) you will also need to check that you aren't accidentally joining inserted with the same record you just inserted. I was always finding a duplicate until I added the equivalent of "i.id <> t.id" to the on clause. – Scott Mitting Oct 27 '15 at 16:49
2

The problem with unique constraint could be its max length (900B) – Petr Aug 17 '16 at 12:18
In my case a unique key would disrupt the balance in a massive congregation of running jobs and stored procs. I just want all that to continue as normal, but avoid the insert without an error being raised. Lots of use cases for this. – ProfK May 05 '17 at 13:26
Scott's comment here contains a **key** piece of information and should be incorporated into this answer - you need to prevent rows in `inserted` matching *themselves* in the target table. – Damien_The_Unbeliever May 05 '17 at 13:57
Thanks @Damien_The_Unbeliever and Scott Mitting for the fix, I've put it in. – Rocklan May 08 '17 at 04:07

score 4 · Answer 2 · edited Nov 12 '15 at 18:42

If you are using a store procedure inserting data into the table, you don't really need a trigger. You first check if the combination exists then don't insert.

CREATE PROCEDURE usp_InsertData
@Name varchar(50),
@Date DateTime
AS
BEGIN

IF (SELECT COUNT(*) FROM tblData WHERE Name = @Name AND Date=@Date) = 0
    BEGIN
        INSERT INTO tblData
                    ( Name, Date)
             VALUES (@Name, @Date)
        Print 'Data now added.'
     END
ELSE
    BEGIN
        Print 'Dah! already exists';
    END
END

The below trigger can used if you are not inserting data via the store procedure.

CREATE TRIGGER checkDuplicate ON tblData
AFTER INSERT
AS

IF EXISTS ( SELECT * FROM tblData A 
INNER JOIN inserted B ON B.name=A.name and A.Date=B.Date)
BEGIN
    RAISERROR ('Dah! already exists', 16, 1);
END
GO

the trigger can raise the error but the data is still inserting into the table, how to stop inserting the data — WiXXeY, Aug 29 '13 at 07:21

score 1 · Answer 3 · answered Apr 13 '20 at 18:34

One more choice, use an insert instead trigger.

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
go

This is a good discussion of how and when to use them.

score 1 · Answer 4 · edited Jun 01 '21 at 08:01

This answer was inspired by the one on Apr 13 '20 at 18:34.

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

score 0 · Answer 5 · answered Aug 29 '13 at 08:23

0

You don’t really need a trigger for this. Just setup a unique constraint for multiple columns.

ALTER TABLE [dbo].[TableName]    
ADD CONSTRAINT [UQ_ID_Name_Date] UNIQUE NONCLUSTERED
(
    [ID], [Name], [Date]
)

answered Aug 29 '13 at 08:23

James Martin

907
8
7

3

Shouldn't the constraint just be on the Name and Date columns? – Rocklan Aug 30 '13 at 01:39

Boss · Answer 6 · 2013-08-30T04:51:30.713

-3

Try this

 CREATE TRIGGER trg ON TableName
 AFTER INSERT 
 AS
  Begin
  Declare @id int,@name varchar(10),@date DateTime
  Declare @cnt1 int,@cnt2 int,@cnt3 int

  INSERT INTO TableName(ID, Name, Date) VALUES(4,'ABC',2011)
  select @id=ID,@name=Name,@date=Date from inserted

  select @cnt2=Count(*) from TableName where Name=@name
  select @cnt3=Count(*) from TableName where Date=@date

  if(@cnt2>1 or @cnt3>1)
    Rollback
  else 
    Commit

  end

edited Aug 30 '13 at 04:51

answered Aug 29 '13 at 06:09

Boss

445
2
8
24

This is full of bugs and won't work. Your insert statement has hard coded values, plus it will not allow data with duplicate names or dates or even IDs. – Rocklan Aug 30 '13 at 01:44
The requirement is not to allow duplicates even different id, right? – Boss Aug 30 '13 at 04:53

Trigger to prevent Insertion for duplicate data of two columns

6 Answers6

Linked