Trigger to prevent Insertion for duplicate data of two columns
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.
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
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.
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