2

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.

  • The error is happening on line 9 which is: Customer_Document__c parent = [SELECT Id FROM Customer_Document__c WHERE Task_ID__c = :a.ParentId Limit 1 ];. Presumably your production org has more than 100,000 Customer_Document__c records in it? – BarCotter May 20 '15 at 13:53
  • but didn´t i prevent to look into them all with line 8? – Ralf Wittenberger May 20 '15 at 13:55
  • No, Task is not a Customer_Document__c. It still has to search all the Customer_Document__c records to find the parent Customer_Document__c for the Task. – crmprogdev May 20 '15 at 14:00
  • so, this would help then? 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 ]; – Ralf Wittenberger May 20 '15 at 14:01
  • 1
    No, the !=null won't help. It still has to search every record for nulls. – crmprogdev May 20 '15 at 14:10
2

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

  • Adrian, thx for your detailed reply, unfortunately, i am totally new to Apex trigger, doing that by try & fail... your comment sounds logic but i can not realise it that way. – Ralf Wittenberger May 20 '15 at 14:04
-1

@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; }}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.