function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
DeekDeek 

Apex Trigger Error Message on Accounts

Hi,

 

Below is the trigger written on Accounts to send email to the old account owner whenever there is a ownership change.

 

The trigger got saved, but from UI when you change the account owner below is the error thrown at line 5 and 6.

 

Error: Apex trigger SendEmailOnOwnerChange caused an unexpected exception, contact your administrator: SendEmailOnOwnerChange: execution of AfterUpdate

caused by: System.QueryException: List has no rows for assignment to SObject: Trigger.SendEmailOnOwnerChange: line 5,6 column 1

 

Please help me out to fix the issue.

 

trigger SendEmailOnOwnerChange on Account (after update) {

    if (trigger.old[0].OwnerId != trigger.new[0].OwnerId) {
        Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

        String emailAddr = [select Email from User where Division != 'NZ' AND Id = :trigger.old[0].OwnerId].Email;
        String newOwnerName = [select Name from User where Division != 'NZ' AND Id = :trigger.new[0].OwnerId].Name;

        String[] toAddresses = new String[] {emailAddr};
        mail.setToAddresses(toAddresses);

        mail.setSubject('ALERT-Owner Changed for Account : ' + trigger.new[0].Name);

        mail.setPlainTextBody('Owner of Account: ' + trigger.new[0].Name + ' Changed to ' + newOwnerName);
        mail.setHtmlBody('Owner of Account: <b>' + trigger.new[0].Name + '</b> Changed to <b>' + newOwnerName  + '</b>');

        Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
    }
}

Best Answer chosen by Admin (Salesforce Developers) 
Kiran  KurellaKiran Kurella

As mentioned by others, the issue is with the select statements. I am assuming you are receiving this error when a user division is not equal to NZ. Try the following trigger:

 

trigger SendEmailOnOwnerChange on Account (after update) {
       if (trigger.old[0].OwnerId != trigger.new[0].OwnerId) {
                   Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

                    User oldUser = [select Email, Division from User where Id = :trigger.old[0].OwnerId];
                    User newUser = [select Name, Division from User where AND Id = :trigger.new[0].OwnerId];

 

                  if(oldUser.Division != 'NZ' && oldUser.Email != null && newUser.Division != 'NZ') {
                             String[] toAddresses = new String[] {oldUser.Email};
                              mail.setToAddresses(toAddresses);
                               mail.setSubject('ALERT-Owner Changed for Account : ' + trigger.new[0].Name);
                           mail.setPlainTextBody('Owner of Account: ' + trigger.new[0].Name + ' Changed to ' + newUser.Name);

 

                          mail.setHtmlBody('Owner of Account: <b>' + trigger.new[0].Name + '</b> Changed to <b>' + newUser.Name + '</b>');

                             Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

          }
     }
}

 

p.s. Please leave your feedback (Kudos), if the suggested solution is working. 

 

All Answers

sushant sussushant sus
may be these two line are not returning value
String emailAddr = [select Email from User where Division != 'NZ' AND Id = :trigger.old[0].OwnerId].Email;
String newOwnerName = [select Name from User where Division != 'NZ' AND Id = :trigger.new[0].OwnerId].Name;

check in system .debug
DeekDeek
Hi Sush,



I believe there is a value for the records I am using for testing.



I now used system.debug but now it throws error. See the below code.
Sorry I am new to apex coding, please help me to modify the code.



Error: Compile Error: Variable does not exist: emailAddr at line 15
column 46





trigger SendEmailOnOwnerChange on Account (after update) {

if (trigger.old[0].OwnerId != trigger.new[0].OwnerId) {

Messaging.SingleEmailMessage mail = new
Messaging.SingleEmailMessage();



try{



String emailAddr = [select Email from User where Division !='NZ'
AND Id = :trigger.old[0].OwnerId].Email;

String newOwnerName = [select Name from User where Division
!='NZ' AND Id = :trigger.new[0].OwnerId].Name;

}



catch (System.QueryException e) {

System.debug('caught ya!');

}



String[] toAddresses = new String[] {emailAddr};

mail.setToAddresses(toAddresses);



mail.setSubject('ALERT-Owner Changed for Account : ' +
trigger.new[0].Name);



mail.setPlainTextBody('Owner of Account: ' + trigger.new[0].Name
+ ' Changed to ' + newOwnerName);

mail.setHtmlBody('Owner of Account: ' + trigger.new[0].Name +
'
Changed to ' + newOwnerName + '');



Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail
});

}

}


Notice



This email and any attachments are strictly confidential and subject to copyright. They may
contain privileged information. If you are not the intended recipient please delete the message
and notify the sender. You should not read, copy, use, change, alter or disclose this email or
its attachments without authorisation. The company and any related or associated companies do
not accept any liability in connection with this email and any attachments including in connection
with computer viruses, data corruption, delay, interruption, unauthorised access or unauthorised
amendment. Any views expressed in this email and any attachments do not necessarily reflect the
views of the company or the views of any of our related or associated companies.
sushant sussushant sus
after this
String emailAddr = [select Email from User where Division !='NZ'
AND Id = :trigger.old[0].OwnerId].Email;

add
system.debug('+++++++++++'emailAddr );
DeekDeek

I have added a null condition if(emailAddr!=null) still i am getting the same error message. How do i modify my code to bypass the error if no records are retrived from the below select statements:

String emailAddr = [select Email from User where Division !='NZ' AND Id = :trigger.old[0].OwnerId].Email;
String newOwnerName = [select Name from User where Division !='NZ' AND Id = :trigger.new[0].OwnerId].Name;

Sharing the code so far:

trigger SendEmailOnOwnerChange on Account (after update) {
if (trigger.old[0].OwnerId != trigger.new[0].OwnerId) {
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();


String emailAddr = [select Email from User where Division !='NZ' AND Id = :trigger.old[0].OwnerId].Email;
String newOwnerName = [select Name from User where Division !='NZ' AND Id = :trigger.new[0].OwnerId].Name;

System.debug('+++++++++++emailAddr');

if(emailAddr!=null){
String[] toAddresses = new String[] {emailAddr};
mail.setToAddresses(toAddresses);

mail.setSubject('ALERT-Owner Changed for Account : ' + trigger.new[0].Name);

mail.setPlainTextBody('Owner of Account: ' + trigger.new[0].Name + ' Changed to ' + newOwnerName);
mail.setHtmlBody('Owner of Account: <b>' + trigger.new[0].Name + '</b> Changed to <b>' + newOwnerName + '</b>');

Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });
}
}
}

Kiran  KurellaKiran Kurella

As mentioned by others, the issue is with the select statements. I am assuming you are receiving this error when a user division is not equal to NZ. Try the following trigger:

 

trigger SendEmailOnOwnerChange on Account (after update) {
       if (trigger.old[0].OwnerId != trigger.new[0].OwnerId) {
                   Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();

                    User oldUser = [select Email, Division from User where Id = :trigger.old[0].OwnerId];
                    User newUser = [select Name, Division from User where AND Id = :trigger.new[0].OwnerId];

 

                  if(oldUser.Division != 'NZ' && oldUser.Email != null && newUser.Division != 'NZ') {
                             String[] toAddresses = new String[] {oldUser.Email};
                              mail.setToAddresses(toAddresses);
                               mail.setSubject('ALERT-Owner Changed for Account : ' + trigger.new[0].Name);
                           mail.setPlainTextBody('Owner of Account: ' + trigger.new[0].Name + ' Changed to ' + newUser.Name);

 

                          mail.setHtmlBody('Owner of Account: <b>' + trigger.new[0].Name + '</b> Changed to <b>' + newUser.Name + '</b>');

                             Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

          }
     }
}

 

p.s. Please leave your feedback (Kudos), if the suggested solution is working. 

 

This was selected as the best answer
DeekDeek

Hi 

 

That worked perfectly. Thanks a million for your timely support and help. Have a nice week ahead.