SQL Server De-duplication Trigger I have a table that contains columns of Country, City, Location, Description. Due to the size of the description field it's not possible to bind the four fields into a composite key to prevent duplication. I have been trying to prevent duplication by way of a trigger but no matter what I try it rolls back every insert, including inserts of unique data.

IF Exists ( Select      a.*
            From        [dbo].[Table] a
                JOIN    Inserted b
                    on  a.Country = b.Country
                    and a.City = b.City
                    and a.Location = b.Location
                    and ltrim(rtrim(a.Description)) = ltrim(rtrim(b.Description))
            )


    BEGIN
        Raiserror   ('The description that you are trying to load already exists.', 16, 1)
        Rollback 
    END


Does anybody have any ideas what is wrong with the above, and what changes I need to make in order to get it to only roll back duplicate inserts?
I think the condition you are checking will always be true as for every insert query executed there will be an entry in the dbo.Table and inserted as well so it will rollback the transaction.

From MSDN


  The inserted table stores copies of the affected rows during INSERT
  and UPDATE statements. During an insert or update transaction, new
  rows are added to both the inserted table and the trigger table. The
  rows in the inserted table are copies of the new rows in the trigger
  table.


I would advice you to check the duplicate before insert as

IF NOT EXISTS (Select a.* FROM [dbo].[Table] a WHERE..............)
  INSERT INTO [dbo].[Table] .......


More Readings : http://msdn.microsoft.com/en-in/library/ms191300.aspx