Trigger - too many rows

Question

The following trigger works well in sandbox, is validated in production, but when I used email to salesforce, it returns the following:

UnexpectedException: MoveAtt: execution of AfterInsert caused by: System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing. Even if a field is indexed a filter might still not be selective when: 1. The filter value includes null (for instance binding with a list that contains null) 2. Data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times) Trigger.MoveAtt: line 9, column 1

Trigger is as follows

trigger MoveAtt on Attachment (after insert) {
    List<Id> forDeletionIds = new List<Id>();
    for (Attachment a : trigger.new){
        String parentIdString = String.valueof(a.parentId);
        if (parentIdString.substring(0,3) == '00T'){
            System.debug(a.parentId);
            if(Task.WhoId != null){
                if(Customer_Document__c.Task_ID__c != null){  
                    Customer_Document__c parent = [SELECT Id  FROM Customer_Document__c WHERE Task_ID__c = :a.ParentId Limit 1 ];
                    if (parent.Id  != null){
                        Attachment body = [SELECT Body FROM Attachment WHERE Id = :a.Id];
                        Attachment newA = New Attachment(
                            Name = a.Name,
                            Body = body.Body,
                            Description = 'Email Attachment from ' + date.today(),
                            OwnerId = a.OwnerId,
                            ParentId = parent.Id
                            );
                        insert newA;
                        forDeletionIds.add(a.Id);
                    }
                }
            }
        }
        List<Attachment> forDeletion = [SELECT Id FROM Attachment WHERE Id IN :forDeletionIds];
        delete forDeletion;
    }
} 

How can I prevent looking for nulls? I don´t know, which Objects causes the 100000 rows.

Answer 1

Take a look at this cheat sheet for a good starting point. If you are querying where ParentId = null, your query will not be selective. In your code you guard against WhoId = null. Also if Task_ID__c is not a lookup field, it will probably not be indexed and your query may be non-selective even if there is only one match.

That said, you should really move your queries out of for loops. Pull all your parent ids into a set and query on that. You can then go back and map by Task_Id__c for easy retrieval while looping over tasks. This will be easier to accomplish and maintain if you move your logic out of the trigger body.

static Set<Id> getParentIds(List<Attachment> attachments)
{
    Set<Id> parentIds = new Set<Id>();
    for (Attachment attachment : attachments) parentIds.add(attachment.ParentId);
    parentIds.remove(null);
    return parentIds;
}

static Map<Id, List<Customer_Document__c>> getDocuments(Set<Id> parentIds)
{
    Map<Id, List<Customer_Document__c>> documents =
        new Map<Id, List<Customer_Document__c>>();
    for (Customer_Document__c document : [
        SELECT Task_ID__c FROM Customer_Document__c
        WHERE Task_ID__c IN :parentIds
    ])
    {
        if (!documents.containsKey(document.Task_ID__c))
            documents.put(document.Task_ID__c, new List<Customer_Document__c>());
        documents.get(document.Task_ID__c).add(document);
    }
    return documents;
}

A few other notes.

When you check Customer_Document__c.Task_ID__c != null that doesn't do anything for your code. Customer_Document__c.Task_ID__c is a Schema.SObjectField and is never null. The same applies to Task.WhoId. You want to make sure you are not querying on null, which I outlined above using collections. If you can't bulkify your trigger, you need to check that a.ParentId != null instead.

Based on my interpretation of your code, you don't really need to delete any Attachments. You are essentially changing two fields, Description and ParentId. Move this all to a before trigger and then just change those two fields on the Attachment

Answer 2

@all

thanks for your comments, i tried the following and it works!

trigger MoveAtt on Attachment (after insert) {
List<Id> forDeletionIds = new List<Id>();
for (Attachment a : trigger.new){
String parentIdString = String.valueof(a.parentId);
if (parentIdString.substring(0,3) == '00T'){
    System.debug(a.parentId);
    if(Task.WhoId != null){
        Customer_Document__c parent = [SELECT Id  FROM Customer_Document__c WHERE Customer_Document__c.Task_ID__c != null and Task_ID__c = :a.ParentId Limit 1 ];
    if (parent.Id  != null){
        Attachment body = [SELECT Body FROM Attachment WHERE Id = :a.Id];
        Attachment newA = New Attachment(
            Name = a.Name,
            Body = body.Body,
            Description = 'Email Attachment from ' + date.today(),
            OwnerId = a.OwnerId,
            ParentId = parent.Id
        );
        insert newA;
        forDeletionIds.add(a.Id);
    }  }  }

List forDeletion = [SELECT Id FROM Attachment WHERE Id IN :forDeletionIds]; delete forDeletion; }}