Trigger recognising data as duplicates when it isn't

Question

I have this trigger in SQL Server:

CREATE TRIGGER MyTrigger ON [dbo].[practiseduplicates]   
AFTER INSERT
AS
    IF EXISTS (SELECT * 
               FROM [practiseduplicates] t 
               INNER JOIN inserted i ON i.[money] = t.[money] 
                                     AND i.[Name] = t.[Name] 
                                     AND i.[year month] = t.[year month])
    BEGIN
        ROLLBACK
        RAISERROR ('Duplicated Data', 16, 1);
    END

I then insert these values (which are already in the data table):

insert into [practiseduplicates]
values ('2017-02', 'buzzlightyear', '10.09')

When I click execute I expected the error message to pop up... which it did, however when I change the values to information that I know is not in the data table

e.g.

'2056-12', 'mr potato head', '12345.09'

The error message still pops up, when in actual fact it should have just inserted the data into the table, does anyone know why this is the case?

I suspect its to do with my inner join statement but I am not sure.

Answer 1

So basically I used a constraint to solve this rather than a trigger statement.

ALTER TABLE [dbo].[practiseduplicates]    
ADD CONSTRAINT [constraintforduplicates] UNIQUE NONCLUSTERED
( [column name],
[column name], etc etc
)

This stops any duplicates using the columns you state in the constraint statement as the data to compare to and flag if they're duplicates and doesn't insert the data into the table.

Note that the columns in the statement have 900 byte constraint themselves. So e.g if you had varchar (max) column, the constraint would not run as the maximum it can do is 900 bytes. In my script I put varchar (800).