+ Start a Discussion
Christina TomaselliChristina Tomaselli 

self-reference from trigger

I have a trigger that I am using to update related accounts (based on a custom field not a lookup) so I am using SOQL but when i try and execute I am getting a self-reference error:

trigger UpdateBusinessAgreement on Account (before update) {
    for (Account a : Trigger.new){
        String TIN = a.Tax_ID_Number__c;
        String AID = a.Id;
        List<Account> matchingAccounts = [SELECT ID, Business_Agreement_Completed__c, Tax_ID_Number__c FROM Account WHERE ID <>: AID AND Tax_ID_Number__c =: TIN];
        if (a.Business_Agreement_Completed__c == 'Received - Complete'){
            for (Account Acc : matchingAccounts){
                    Acc.Business_Agreement_Completed__c = 'Received - Complete';
                    update acc;
            }
        }
    }
}
ShirishaShirisha (Salesforce Developers) 
Hi Christina,

Greetings!

You do not need to write the logic to retrieve the Accounts as the records are already there in the Trigger.New.So,can you please try by skipping the Select Statement and update the Accounts records directly.

Reference:https://developer.salesforce.com/forums/?id=906F00000008ziKIAQ

Kindly mark it as best answer if it helps so that it can help others in the future.

Warm Regards,
Shirisha Pathuri
Christina TomaselliChristina Tomaselli
The SOQL query is to pull all account records with the same Tax ID number so I can update those records, but I can't seem to exclude the original trigger record.
David Zhu 🔥David Zhu 🔥
A few point you may have to change.
1. Do not put SOQL in the loop. This is the best practie recommened by Salesforce. Otherwise, you will easily get SOQL 101 issue when doing batching process.
2. Use AFTER Update in this case, because you are going to change other records other than the updating records themself.

Please try the code below:
trigger UpdateBusinessAgreement on Account (after update) {
    List<Id> TINs = new List<ID>();
    for (Account a : Trigger.new){
        TINs.add(a.Tax_ID_Number__c);
    }

    List<Account> updateAccounts = new List<Account>();

    List<Account> matchingAccounts = [SELECT ID, Business_Agreement_Completed__c, Tax_ID_Number__c 
            FROM Account WHERE Tax_ID_Number__c in :TINs];

    for (Account acc : trigger.new) {
        if (acc.Business_Agreement_Completed__c == 'Received - Complete')  {
            for (Account matchingAcc :matchingAccounts){

                if (acc.Tax_ID_Number__c == matchingAcc.Tax_Id_Number__c && acc.Id != matchingAcc.Id)  {
                    matchingAcc.Business_Agreement_Completed__c = 'Received - Complete';
                    updateAccounts.add(matchingAcc);
                }
            }
        }
    }

    if (updateAccounts.size() > 0)  {
        update updateAccounts;
    }
}

 
Andrew GAndrew G
There is some risk of recursion with this sort of trigger, so some extra filtering may come in handy
 
trigger UpdateBusinessAgreement on Account (after update) {
    List<Id> TINs = new List<ID>();
    for (Account a : Trigger.new){
//we only want to record the tax number for accounts that are 'received - complete'
        if( a.Business_Agreement_Completed__c == 'Received - Complete'){
            //this way we only run the code it completed
            TINs.add(a.Tax_ID_Number__c);
        }
    }

//if we have records to make, lets find them
    if( TINS.size() > 0 ) {
        List<Account> updateAccounts = new List<Account>();

//query those records with matching Tax IDs, and Status is NOT 'Received - Complete and not in the original trigger
        List<Account> matchingAccounts = [SELECT ID, Business_Agreement_Completed__c, Tax_ID_Number__c 
            FROM Account 
            WHERE Tax_ID_Number__c in :TINs 
                AND Business_Agreement_Completed__c <> 'Received - Complete'
                AND Id NOT IN :trigger.new];
//simplify the loop, rather than loop inside a loop
        if(matchingAccounts.size()>0){
            for (Account matchingAcc :matchingAccounts){
                matchingAcc.Business_Agreement_Completed__c = 'Received - Complete';
                updateAccounts.add(matchingAcc);
            }
        }    
        if (updateAccounts.size() > 0) { 
            update updateAccounts; 
        } 
    }

}

some reasoning.

1.  it seems to be that we want to move to a state of "received - complete".  If we are already at that status, why do another update which will risk kicking things into a recursive loop.  So test for the status being already set and only update those values that require the update
2.  to exclude records that fire the original trigger, we can use a NOT IN as a filter for the SOQL.

so based on the above, if we consider updating a record 1, (and assume there is a 2nd record in database), the trigger fires, works out the status change and then goes and finds the other record.  The query will return only that record.   Once that record is then updated by the after trigger, the update trigger will fire again.  The first loop will run.  And then the SOQL query will run, but return no values.  And therefore no more DMLs will run and the risk of recursion is avoided.


regards
Andrew