asked on

SQL Server Trigger to prevent duplicate inserts

hello expert
I have a person table.
I don't want duplicate inserts,
ı want to use SQL Server Trigger to prevent duplicate inserts.
for example

CREATE TABLE person(
    tc_umarasi varchar(11),
     adi VARCHAR (50) ,
    soyadi VARCHAR (50) 
);

Open in new window

I tried to write a trigger
trigger is as follows

create TRIGGER [dbo].[dene_ali]
   ON [dbo].[person] FOR INSERT
AS
IF EXISTS(SELECT I.adi
		From Inserted I, person F
		Where  I.tc_umarasi=F.tc_umarasi AND I.adi = F.adi  
		Group By I.tc_umarasi,I.adi
		Having COUNT(*) >1 )
BEGIN
   
  RAISERROR('>>>DUPLICATES PREVENTED<<< ',10,1)
    ROLLBACK TRAN
	
END

Open in new window

trying to add a few data
collectively when I insert

insert into person(tc_umarasi,adi,soyadi) values('11','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('88','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('33','ali sahin','bayoglu');

Open in new window

then I run the following query

SELECT * FROM person

Open in new window

tc_umarasi        adi        soyadi        id
11        ali sahin      bayoglu      NULL
125        ali sahin      bayoglu      NULL
88        ali sahin      bayoglu      NULL

insert into person(tc_umarasi,adi,soyadi) values('33','ali sahin','bayoglu');-want this value to be printed in the database.
I want this value to be printed in the database.
but the database stoped the insert operation before it reaches this value.
When trigger encounters duplicate, I don't want to write value to the table, but I want it to continue with other insert operations.
how can I resolve this?
thanks a lot

Joshua Kinsel

Why don't you create a unique key constraint in the person table on tc_umarasi and disable trigger?
Example:
ALTER TABLE [dbo].[person]
ALTER COLUMN tc_umarasi int;

ALTER TABLE [dbo].[person]
ADD CONSTRAINT [UQ_ID_tc_umarasi] UNIQUE NONCLUSTERED
(
[tc_umarasi]
)
Also change data type to int, smallint, bigint (choose data type by expected number of records)
Or drop table and use:

CREATE TABLE person(
tc_umarasi int primary key not null,
adi VARCHAR (50) ,
soyadi VARCHAR (50)
);

ali şahin

ASKER

thanks but,
there are repeating records in the table (duplicate record).
I am not authorized to delete these records.
so I can't use any constraints.
I have to solve this problem without using constraint. :(

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

The tricky situation is when a row doesn't currently exist and there are multiple entries for it in the inserted table, like the example you gave:

insert into person(tc_umarasi,adi,soyadi) values('11','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('88','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('125','ali sahin','bayoglu');
insert into person(tc_umarasi,adi,soyadi) values('33','ali sahin','bayoglu');

You said you wanted the '33' value. How is the trigger to determine that? The last value entered? Is there a datetime value that can be used to determine that? Otherwise, the code would have to cursor through the rows to get the last one and that would be huge overhead.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joshua Kinsel

Try this: I didn't test but here you go:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create TRIGGER [dbo].[trg_insertNotDups]

ON [dbo].[person]

FOR

INSERT

AS

declare @id varchar(11);

declare @col2 varchar(50);

declare @col3 varchar(50);

select @id=i.[tc_umarasi] from inserted i;

select @col2=i.adi from inserted i;

select @col3=i.soyadi from inserted i;

if @id not in (select [tc_umarasi] from dbo.person)

Begin

insert into dbo.person

(tc_umarasi, adi, soyadi)

values (@id, @col2, @col3)

End

ali şahin

ASKER

https://www.experts-exchange.com/questions/29163783/SQL-Server-Trigger-to-prevent-duplicate-inserts.html?anchorAnswerId=42977201#a42977201

I tried this solution today.
I use the bulkinsert () method in my program.
While saving the list, it does not add any records and prevents the exception from being repeated.
this solution works for non-repeating list.
mssql throws error in repetitive data.
How can I ignore this error in mssql.
because I want it to continue for the next record even though there is an error.
How do I continue this process even though it throws an exception?

Thanks.

ali şahin

ASKER

https://www.experts-exchange.com/questions/29163783/SQL-Server-Trigger-to-prevent-duplicate-inserts.html?anchorAnswerId=42977264#a42977264

Thanks,

I am going to try this solution .

ste5an

How can I ignore this error in mssql.
because I want it to continue for the next record even though there is an error.
How do I continue this process even though it throws an exception?

In SQL: You simply don't. It would violate ACID.

The only solution in SQL is not let that exception happen. Thus when you want to it via trigger, use an INSTEAD OF INSERT trigger and insert only the rows, which won't violate your imaginary constraint.

ali şahin

ASKER

thanks for everything