Cross-Object SOQL Query

Question

I have a set of custom fields on my Accounts object that depict weather an Account is approved for business with our company. The Approved Date is added via an Approval Process, and Account Status is updated with a simple Time Based workflow. My Goal is to add a time base to send renewal emails to contacts that i mark as "Admin Contacts" or personel like CEO or CFO of the companys we do business with.

i would like to just add the Account Expiration date to each Admin contact, i have achieved this if i place a check mark on the contact. but if a new renewal is initiated the date on the contact wont update unless i uncheck and recheck each admin.

i wrote a trigger and class to achieve this and have tested it many ways and it doesnt update any of the contacts once the Approval Date is changed on the account record.

heres what the Account fields look like:

Contact Fields:

heres my class:

    public with sharing class AdmContAcct
{
    public void leadAlerts(List<Account> lstAcct)
    {
        sendAlertsPrivate(lstAcct);
    }

    private void sendAlertsPrivate(List<Account> lstAcct)
    {
        List<Contact> lstUpdateCon = new List<Contact>();

        for(Account acc : lstAcct)
        {
            List<Contact> lstCon = [SELECT Id, Admin_User__c, Account_Expiration__c FROM Contact WHERE Id = :acc.Id AND Admin_User__c = true];  

            if(acc.Approved_Date__c != null)
            {
                Date tdy = date.today();
                Date expire = acc.Approved_Date__c.addDays(365);
                boolean approved = expire >= tdy;
                if(!lstCon.isEmpty())
                {
                    for(Contact conLoop : lstCon)//loop through contacts with admin user related to account passed through.
                    {
                        if(approved)
                        {
                            lstUpdateCon.add(new Contact(Account_Expiration__c = expire));
                            //conLoop.Account_Expiration__c = expire;
                            //update conLoop;
                        }
                        else //account not approved
                        {
                            lstUpdateCon.add(new Contact(Account_Expiration__c = null));
                            //conLoop.Account_Expiration__c = null;
                            //update conLoop;
                        }
                    }
                }
            }           
            else//Approved date removed
            {
                if(!lstCon.isEmpty())
                {
                    for(Contact conLoop : lstCon)
                    {
                        lstUpdateCon.add(new Contact(Account_Expiration__c = null));
                        //conLoop.Account_Expiration__c = null;
                        //update conLoop;
                    }
                }
            }
        }
        if(!lstUpdateCon.isEmpty())
        {
            update lstUpdateCon;
        }       
    }
}

Trigger:

    trigger AdmContAcct on Account (after update) 
{
    AdmContAcct objAcctHandler = new AdmContAcct();

    if (Trigger.IsUpdate)
    {
        objAcctHandler.leadAlerts(Trigger.New);
    }

}

i get no errors when saving in the Eclipse IDE but i get no results when updating the account fields.

my questions are:

• Did i incorrectly state my SOQL query? if so how can it be fixed?
• did i incorrectly Loop the contacts?
• Should this have been a before trigger? i have tried it both ways before and after. if it is supposed to be an after trigger did i write the update statements correctly?

Update:

• as Per Adrian i understand that After is correct, he stated that Cross-Object Queries must be After Trigger.

• how can i get the SOQL query out of the loop? and what do i have to do to fix it?

• will the loop work if i get the query out of the loop?

Answer 1

Answering your questions only:-

Did i incorrectly state my SOQL query?

Yes, SOQL in for-loop is highly not recommended. What if the for loop cross 100 records. You will get SOQL 101. So first improvement in the code would be the take out all queries from for loop.

Did i incorrectly Loop the contacts?

There is no bulkification. You must query all Contacts together. Query all Contacts for all Accounts. Keep then in collection and access from the Map only. Collection must be used for such situations.

Should this have been a before trigger? i have tried it both ways before and after. if it is supposed to be an after trigger did i write the update statements correctly?

Email operation would be sent on After events only. Your update DML is in for-loop so, again an issue (DML 151).

Note: Go though links provided with each answers to avoid these issues.

Answer 2

Apart from not bulkifying your code, it looks like you are not referencing the account in your where clause. Try the below.

List<Contact> lstCon = [SELECT Id, Admin_User__c, Account_Expiration__c FROM Contact WHERE AccountId = :acc.Id AND Admin_User__c = true];