My objective is inserting the first insert, but not letting the second to pass, because NIC is duplicated. I don't know why, but it isn't letting the first one pass without having other NIC to compare if it already exists one equal.
I know I can prevent duplicates with "unique", but I was trying to do with a trigger :/
Create table Utentes
(
numUtente nchar(3),
Name nchar(40) not null,
NIC nchar(8) not null,
Constraint PK_Utente Primary Key(numUtente)
)
create trigger tr_Duplicate
on Utentes
after insert
as
declare @NIC nchar(8)
select @NIC = NIC from inserted
if exists(select * from Utentes where NIC = @NIC)
begin
print 'NIC already in database'
rollback
end
go
insert into Utentes (numUtente, Name, NIC)
values ('123', 'asd', '12345678')
insert into Utentes (numUtente, Name, NIC)
values ('124', 'asd', '12345678')
select * from Utentes
Result:
NIC already in database
Msg 3609, Level 16, State 1, Line 1392
The transaction ended in the trigger. The batch has been aborted.
You should really use a constraint. An "after insert" trigger will actually put the second row in the table . . . and hopefully no one is using
NOLOCK
for reading it.In any case, you have to actually count the rows and look for multiple occurrences. It would be something like this:
With an
instead of
trigger, you would not add new rows into the table if one already exists: