CODEWITHSUNDEEP
sqlsql-serversql-server-2008
WiXXeY
WiXXeY

Reputation: 1001

Trigger to prevent Insertion for duplicate data of two columns

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.

Upvotes: 13

Views: 56110

Answers (6)

Aziz Hani
Aziz Hani

Reputation: 11

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

Upvotes: 1

Display name
Display name

Reputation: 1542

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.

Upvotes: 1

Rocklan
Rocklan

Reputation: 8130

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.

Upvotes: 24

January Mmako
January Mmako

Reputation: 330

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

Upvotes: 4

Boss
Boss

Reputation: 475

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

Upvotes: -3

James Martin
James Martin

Reputation: 907

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]
)

Upvotes: 0

Related Questions