Prevent duplicates with trigger SQL

Question

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.

Answer 1

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:

Create trigger tr_Duplicate on Utentes after INSERT as
begin    
    if exists (select 1
               from utentes u join
                    inserted i
                    on u.nic = i.nic
               group by u.nic
               having count(*) > 1
              )
    begin
        print 'NIC already in database';
        rollback;
    end;
end;

With an instead of trigger, you would not add new rows into the table if one already exists:

create trigger tr_Duplicate on Utentes after INSERT as
begin    
    if exists (select 1
               from utentes u join
                    inserted i
                    on u.nic = i.nic
              )
    begin
        print 'NIC already in database';
        rollback;
    end;
    else
    begin
        insert into utentes
            select i.*
            from inserted i;
    end;
end;

Answer 2

I would second the sentiment against the use of triggers and would also suggest using UNIQUE constraints. In my humble opinion, I would rather search for a solution in the ETL layer, grouping records as they are inserted. With triggers you will get the aforementioned concurrency and consistency issues, as well as potentially swelling your tempdb or T-log if the table ever gets big enough to take some time to process.