Trigger Before Insert - Comparing to Lists

Question

I have a set of records I would like to load into my Account object. I'm trying to write a trigger before insert/update to validate the Bus. Id and Account Name already exists in SFDC or not. Because our data is a mess, Bus. ID and Account are not unique.

We should check incoming Bus. Id against Bus. ID in SFDC, if there is a match then we check the Account Name. If both matches then no further actions needed.

If Bus. Id and Account do match then insert record into SFDC.

If Bus. Id match and Account Name doesn't then update the record in SFDC with the incoming Bus. Id.

Can some experts help with the structure of this trigger so I can follow and modify if needed?

This is what I have so far:

trigger Data_Transfer_BI_SFDC on Account (before insert){

    List<String> accountupdate = new List<String>();
    List<account> accountinsert = new List<account>();
    List<String> accountNames = new List<String>();

    List<Account> SF_Account = [select id, Service__c, Business_ID__c, name from Account order by Business_ID__c desc];

    for (Account a : Trigger.New){

        accountNames.add(a.Service__c);
        accountNames.add(a.Business_ID__c);
        accountNames.add(a.name);

    }
}

Answer 1

Sounds like you want to perform an upsert based on an external id field (Business_ID__c) and the account name.

If you could populate a custom field with these two values and set it as an external id then you could let Salesforce do the hard work for you. If the upsert matches on the resulting external id custom field then the existing record will be updated. Otherwise a new record will be inserted.

---

With regards to the trigger. I don't think you can convert an insert into an update on the fly. You will need to reject the records and handle them separately as updates instead.

trigger Data_Transfer_BI_SFDC on Account (before insert){

    // Find the unique Business Ids in the transaction
    Set<String> businessIds = new Set<String>();
    for (Account a : Trigger.New) {
        if(a.Business_ID__c != null) { 
            businessIds.add(a.Business_ID__c);
        }
    }

    // Get the existing accounts for the business ids
    Map<string, Account> businessIdToAccountMap = new Map<string, Account>();
    for (Account a : [select id, Business_ID__c, name 
                             from Account 
                             where Business_ID__c in :businessIds]) {
        businessIdToAccountMap.add(a.Business_ID__c, a);
    }

    // Find any of the Accounts being inserted that match an existing account by
    // Business Id and name. Reject these records.
    for (Account a : Trigger.New) {
        Account existingAccount = businessIdToAccountMap.get(a.Business_ID__c);
        if(existingAccount != null && existingAccount.Name == a.Name) {
            a.addError('Matches existing Account: ' + existingAccount.Id);
        }        
    }
}