+ Start a Discussion

List has no rows for assignment to SObject

Hi everyone.

Can you please help me to fix this error: I have a trigger that would send an email alert to new Assigned To user. 

here is the code: 

trigger SendEmailAlertToAssignedTo on Site_Defects__c ( before update) {
    if(Trigger.new[0].Assigned_To__c != Trigger.old[0].Assigned_To__c ) {
            //Sending Mail
            Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage() ;
                //Setting user email in to address
            String userName = Trigger.new[0].Assigned_To__c;
            User AT = [Select Email From User where name = : userName ];  // I have the error message here
            String userEmail = AT.Email;
            //String email = Trigger.new[0].RegionalManager__c;
            String[] toAddresses = new String[] {userEmail} ;
            // Assign the addresses for the To and CC lists to the mail object
            mail.setToAddresses(toAddresses );
            //Email subject to be changed
            mail.setSubject('THIS IS A TEST. Site Defect Owner Changed');
            String body = AT.Email+'The owner of Site Defect ' + trigger.Old[0].Name +' has been changed <br> <br>'+
            'Regards <br> <br>'+
            'Phillips 66 Limited <br>'+
            'Phillips 66 Centre <br>'+
            '2 Kingmaker Court <br>'+
            'Warwick Technology Park<br> '+
            'Warwick, CV34 6DB. <br> <br> <br>'+
            'Phillips 66 Limited is a company registered in England with Company No. 529086 <br>'+
            'Registered Office: 7th Floor, 200-202 Aldersgate Street, London, EC1A 4HD.<br>';
            //Body of email
            //Sending the email
            Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

Thank you
Hi Klivingston,

If Assigned_To__c is a lookup field then you will be getting an RowId, if your trying to search by giving the condition on Name then you will not get any records. that is the reason it is giving an error. Try changing the where condition as Id instead of name like below and try it

Select Email From User where Id= : userName
Hi Swaroopa

Thank you very much, that did fix the error. The trigger works fine when the change happens to an existing record but when I create a new record it show this error:

System.NullPointerException: Attempt to de-reference a null object: Trigger.SendEmailAlertToAssignedTo: line 3, column 1

I assume it's because the Trigger.old[0].Assigned_To__c value is null. Can you please advise how I can fix that.

Hi Klivingston,

Yes it is bcoz of Trigger.old[0].Assigned_To__c value is null. If you need to send an email in case of new record creation. You can check the condition in AfterInsert whether the value of Assigned_To__c is null or not.

Iterate all newly created Users and check whether Assigned_To__c value is null or not. If not null then add all users in a list. After that query Email field from user by passing the list and then send an email.

Don't call sendEmail method for every user bcoz you will reach governor limits. Add all Mail objects in a  list and in the last call the sendEmail method by passing list