Duplication prevention logic need for trigger bulk insert?

Question

I wrote a trigger to find the duplicate and add error in the trigger.Before insert context

Set<string> appnameSet = new set<string>();
for(test1__c test : trigger.new)
{
   appnameSet.add(test.appName__c);
}

List<test1__c> testList = new List<test1__c >();

for(test1__c  tt: [select appName__c from Test1__c where APPName__c IN :appnameSet])
{
      for(test1__c  tt1: trigger.new) 
      {
          if(tt1.appName__c == tt.appName__c )
          {
               tt1.addError('Duplicate app name found') ;
          }
      }

}

Its working as expected for single record.

But when i insert the data in bulk

List<Test1__c> testListinsert = new List<Test1__c>()
testListinsert.add(new Test1__c(name="One",appName__c = 'APP1'));
testListinsert.add(new Test1__c(name="Two",appName__c = 'APP1')); //Duplicate
testListinsert.add(new Test1__c(name="Three",appName__c = 'APP1'));//Duplicate
insert testListinsert;

So it should check within this bulk records. it should allow record "One" and it should add error for "Two" and "Three".

We want that logic to be in trigger. Any idea?

Answer 1

I think the problem here is that in your trigger you check for the data in the table, but the data is not in the table yet at the time you execute the trigger, because the bulk insertion is in the same transaction, so, as have been suggested, if you need the logic to be in the trigger, you will need to check the entire list in the transaction that has been inserted. But if you don't need to have this validation in the trigger, you can make the field unique or use the duplication rules to create some logic for this validation.

Answer 2

I could think of writing this kind of logic

1. check the bulk list which is getting inserted contains duplicates, if so, then throw the error.
2. Otherwise, take the list of appnames and try to find out if database contains records with that appnames through Aggregate query specially using GROUP BY HAVING clause.

Trigger Code

Map<String, String> mapDup = Map<String, String>();

//first level filter if existing records to be inserted contains duplicates.
for(test1__c test : trigger.new)
{
   mapDup.containsKey(test.appName__c)
   {
       test.addError('Duplicate app name found') ;     
   }
   else
   {
       mapDup.put(test.appName__c, test.appName__c);
   }
}

//now check if records to be inserted is exist in database.
AggregateResult[] lstResult = [ SELECT APPName__c, COUNT(Id), 
                                FROM Test1__c
                                WHERE APPName__c IN:mapDup.keySet()
                                GROUP BY APPName__c
                                HAVING COUNT(Id)>0];

if(lstResult.size()>0)
{
    trigger.new[0].addError('Duplicate app name found');

}