Trigger to prevent Insertion for duplicate data of two columns

sql, sql-server, sql-server-2008

Solution

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.

Problem

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.

Original source