+ Start a Discussion
JayMoiahJayMoiah 

Bulk Trigger before insert, before update

Ok all here is one that I am running into Trouble with. I have a Trigger that is Before insert, before update.

It is validating the address via the postalcodes as well as ensuing a proper grouping for reporting using the County and State returned from the Zip look up.
It works great however I need to Accommodate for passing 2300+ accounts at one time.
Any one do a Bulk/Batch processing before insert, before update? I keep getting a recursive issue.


Best Answer chosen by Admin (Salesforce Developers) 
JayMoiahJayMoiah

Solution was reached, Thank you all for your input both here and on Dreamforce Chatter

I was able to resolve it by moving a few of the queries around and then Adding a Nullpointer caputer for validating the Zip.

Code ( Appologize on the format had to shrink it):

trigger Account_Set_Plan_Site on Account (before insert, before update)
{
//Loop to collect all the Account id and store in a set/list
 Set<ID> actOwnerIDs = new Set<ID>();
 Set<ID> siteIDs = new Set<ID>();
 Set<String> ZipCodeNames = new set<String>();
 Set<ID> RTIds = new set<ID>();
 Integer I=0;
 //Get all the Account Owners and Site Ids if Pla Site is Filled
 for (Account acct : Trigger.new)
 {
 ActOwnerIds.add(acct.OwnerID);
 RTIds.add(acct.RecordtypeId);
 if (acct.Plan_Site__c != null ) {
 siteIDs.add(acct.Plan_Site__c);
 }
 if (Acct.ISPERSONACCOUNT!=True)
 {
 if (Acct.BILLINGPOSTALCODE!= null ) {
 ZipCodeNames.add(Acct.BILLINGPOSTALCODE);
 }
 } else {
 if (Acct.PERSONMAILINGPOSTALCODE!= null ) {
 ZipCodeNames.add(Acct.PERSONMAILINGPOSTALCODE);
 }
 }
 }

 Map<ID,RecordType> RTypeMap;
 RTypeMap=New Map<ID,RecordType>([Select id, Name from RecordType Where Id in :RTIds]);

 
 // Collect the siteName and Division per the Account OwnerID
 set<String> sName = new set<String>(); // Set created for the passing of then Owner Site_Name__c
 List<Site__c> lSite = new List<Site__c>();
 List<User> uList = [Select Division, Site_Name__c from User where Id in:actOwnerIds];
 Map<Id, Site__c> uSiteNames = new Map<Id, Site__c> (); //Map for Site__c of the Owner based on the Site_Name__c
 Map<Id, string> uDivisions = new Map<Id, string> (); // Map for the Owner Division
 Map<id,String> lName = new Map<id,String>(); //
 
// Loop to Put the OwerID into LName and add the Text String to the Set of sName
 for(user u : ulist){
 lName.put(u.id,u.Site_Name__c);
 sName.add(u.Site_Name__c);
 }
// List base on collected Site_Name__c collected from the Owners user record Site_Name__c add to the Previous Loop
 lSite = [select Id,
 Name,
 Medicaid_Site_Manager__c,
 Medicare_Site_Manager__c,
 PR_Site_Manager__c,
 Medicaid_Calendar_ID__c,
 Medicare_Calendar_ID__c,
 PR_Calendar_ID__c
 From Site__c
 where Name in :sName ];
//Loop to directly relate the Site__c.Id to the OwnerId of the record
 for(user u : ulist){
 for(Site__c s:lSite){
 if(lName.get(u.id) == s.Name){
 uSiteNames.put(u.id,s);
 }
 }
 uDivisions.put(u.id, u.Division);
 }
//Use the Account.Plan_Site__c to obtain site id
 Map<Id, Site__c> siteMap;
 if (siteIDs.size() > 0) {
 //obtain the site record based on Account.Plan_Site
 siteMap = new Map<Id, Site__c>([select Id,
 Medicaid_Site_Manager__c,
 Medicare_Site_Manager__c,
 PR_Site_Manager__c,
 Medicaid_Calendar_ID__c,
 Medicare_Calendar_ID__c,
 PR_Calendar_ID__c
 From Site__c where Id in :siteIDs]);
 }
 List<State__c> states = [Select ID,NAME,CITY_NM__C,COUNTY_CD__C,COUNTY_NM__C,STATE_CD__C,BOROUGH__C From State__c where Name in : ZipCodeNames];
 Map<String,State__c> ZipCodeMap = new Map<String,State__c>();
 for(State__c state: states)
 {
 ZipCodeMap.put(State.Name,state);
 }

 if (Trigger.isInsert ||Trigger.isUpdate ){
 for (Account act : Trigger.new)
 {
 if (RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care' && act.No_Address__c==True && (act.PERSONMAILINGStreet!=Null||act.PERSONMAILINGPOSTALCODE!=Null || act.PERSONMAILINGCITY!=Null ||act.PERSONMAILINGSTATE!=Null ||act.PERSONMAILINGCOUNTRY!=Null ||act.PLan_State__c!=null||act.County__c!=null))
 {
 act.No_Address__c.addError('You are not allowed to have a Partial Address Please Uncheck and Complete Address or Remove Values from Address Fields.');
 }
 if ((RTypeMap.get(act.RecordTypeID).Name=='Member Read-Only Care' && act.No_Address__c!=True) && (act.PERSONMAILINGStreet==Null||act.PERSONMAILINGPOSTALCODE==Null))
 {
 act.No_Address__c.addError('You are not allowed to have a Partial Address. Please Complete Mailing Street and or Enter the Proper MAILING POSTALCODE to complete the Address or Remove Values from Address Fields and Check No Address.');
 }
 
 if (act.ISPERSONACCOUNT!=True)
 {
 Try {if(act.BILLINGPOSTALCODE != null ){
 I=ZipCodeMap.get(act.BILLINGPOSTALCODE).Name.length();
 }
 }catch (System.NullPointerException e) {
 }
 if (act.BILLINGPOSTALCODE != null && I>1) {
 act.BILLINGCITY=ZipCodeMap.get(act.BILLINGPOSTALCODE).CITY_NM__C;
 act.BILLINGSTATE=ZipCodeMap.get(act.BILLINGPOSTALCODE).STATE_CD__C;
 act.BILLINGCOUNTRY='USA';
 act.County__c=ZipCodeMap.get(act.BILLINGPOSTALCODE).COUNTY_NM__C;
 act.COUNTY_CODE_3__C=ZipCodeMap.get(act.BILLINGPOSTALCODE).COUNTY_CD__C;
 act.BOROUGH__C=ZipCodeMap.get(act.BILLINGPOSTALCODE).BOROUGH__C;
 act.Plan_state__c=ZipCodeMap.get(act.BILLINGPOSTALCODE).STATE_CD__C;
 } else {
 if(act.BILLINGPOSTALCODE != null && I==0){
 act.BILLINGPOSTALCODE.addError('Please Enter the Proper Billing PostalCode for this Account.');
 }
 }
 } else {
 Try {if(act.PERSONMAILINGPOSTALCODE != null ){
 I=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).Name.length();
 }
 }catch (System.NullPointerException e) {
 }
 if (act.PERSONMAILINGPOSTALCODE != null && I>1) {
 act.PERSONMAILINGCITY=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).CITY_NM__C;
 act.PERSONMAILINGSTATE=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).STATE_CD__C;
 act.PERSONMAILINGCOUNTRY='USA';
 act.County__c=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).COUNTY_NM__C;
 act.COUNTY_CODE_3__C=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).COUNTY_CD__C;
 act.BOROUGH__C=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).BOROUGH__C;
 act.Plan_state__c=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).STATE_CD__C;
 } else {
 if(RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care' && act.No_Address__c!=True && I==0){
 act.PERSONMAILINGPOSTALCODE.addError('Please Enter the Proper MAILING POSTALCODE for this Account. Or Check No Address');
 }
 }
 
 }
 act.Owner_Division__c = uDivisions.get(act.Ownerid);
 if (RTypeMap.get(act.RecordTypeID).Name!= 'Member Read-Only Care' )
 {
 if (uSiteNames.get(act.OwnerId)!= null) {
 act.Plan_Site__c = uSiteNames.get(act.OwnerId).Id;
 } else {
 act.Plan_Site__c.addError('There is not a default Site linked to the User record. Please select an Event Site in the Account below or ask your administrator to assign a default site.');
 }
 
 } else if (RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care'){
 if (act.Plan_State__c=='NJ' && act.County__c=='Bergen') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Essex') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Hudson') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Middlesex') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Monmouth') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Ocean') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Union') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Bronx') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Kings') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='New York') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Queens') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Richmond') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Davidson') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Maury') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Rutherford') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Williamson') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Denton') {act.Plan_site__c='a0980000006IB3nAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Tarrant') {act.Plan_site__c='a0980000006IB3nAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Bexar') {act.Plan_site__c='a0980000006IB3sAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Harris') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Brazoria') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Fort Bend') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Montgomery') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Bernalillo') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Otero') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Sandoval') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Santa Fe') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Socorro') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Torrance') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Valencia') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Hillsborough') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Orange') {act.Plan_site__c='a0980000006IB0EAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Pasco') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Pinellas') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Polk') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Broward') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Miami-Dade') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Palm Beach') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Anne Arundel') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Baltimore City') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Baltimore') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Carroll') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Harford') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Howard') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Montgomery') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Prince George') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='GA' && act.County__c=='Chatham') {act.Plan_site__c='a0980000006IB1bAAG';}
 else if (act.Plan_State__c=='GA' && act.County__c=='Fulton') {act.Plan_site__c='a0980000006IB0iAAG';}
 else{if (uSiteNames.get(act.OwnerId)!= null)
 {
 act.Plan_Site__c = uSiteNames.get(act.OwnerId).Id;
 } else {
 act.Plan_Site__c.addError('There is not a default Site linked to the User record. Please select an Event Site in the Account below or ask your administrator to assign a default site.');
 }
 }
 }
 System.debug(act);
 }
 }
}

All Answers

VarunSforceVarunSforce

Please mention, specific error. I think in general preapring data MAP before records loop will be helpful.

UVUV

Can you please post the code?

This may help you to control the recursiveness of the trigger.

http://www.salesforce.com/docs/developer/cookbook/Content/apex_controlling_recursive_triggers.htm

Pragadheeshwari APragadheeshwari A

Do not use queries inside the for loop. Instead u can use list,set etc., If you quering the same object which you have the trigger, dont forget to remove the records in trigger.New.  We should not perform dml operations for trigger.New Records(Currently Processing)

JayMoiahJayMoiah

As Requested Here is the Code that is Working when creating up to 10 accounts anything beyound too many SOQL Errors. Then when I try to batch it up I run into issues. I appologize for the format this would not allow me to post how it was written.

trigger Account_Set_Plan_Site on Account (before insert, before update)
{
//Loop to collect all the Account id and store in a set/list
 Set<ID> actOwnerIDs = new Set<ID>();
 Set<ID> siteIDs = new Set<ID>();
 Set<String> ZipCodeNames = new set<String>();
 Set<ID> RTIds = new set<ID>();
 //Get all the Account Owners and Site Ids if Pla Site is Filled
 for (Account acct : Trigger.new)
 {
 ActOwnerIds.add(acct.OwnerID);
 RTIds.add(acct.RecordtypeId);
 if (acct.Plan_Site__c != null ) {
 siteIDs.add(acct.Plan_Site__c);
 }
 if (Acct.ISPERSONACCOUNT!=True)
 {
 if (Acct.BILLINGPOSTALCODE!= null ) {
 ZipCodeNames.add(Acct.BILLINGPOSTALCODE);
 }
 } else {
 if (Acct.PERSONMAILINGPOSTALCODE!= null ) {
 ZipCodeNames.add(Acct.PERSONMAILINGPOSTALCODE);
 }
 }
 }

 Map<ID,RecordType> RTypeMap;
 RTypeMap=New Map<ID,RecordType>([Select id, Name from RecordType Where Id in :RTIds]);

 
 // Collect the siteName and Division per the Account OwnerID
 set<String> sName = new set<String>(); // Set created for the passing of then Owner Site_Name__c
 List<Site__c> lSite = new List<Site__c>();
 List<User> uList = [Select Division, Site_Name__c from User where Id in:actOwnerIds];
 Map<Id, Site__c> uSiteNames = new Map<Id, Site__c> (); //Map for Site__c of the Owner based on the Site_Name__c
 Map<Id, string> uDivisions = new Map<Id, string> (); // Map for the Owner Division
 Map<id,String> lName = new Map<id,String>(); //

// Loop to Put the OwerID into LName and add the Text String to the Set of sName
 for(user u : ulist){
 lName.put(u.id,u.Site_Name__c);
 sName.add(u.Site_Name__c);
 }
// List base on collected Site_Name__c collected from the Owners user record Site_Name__c add to the Previous Loop
 lSite = [select Id,
 Name,
 Medicaid_Site_Manager__c,
 Medicare_Site_Manager__c,
 PR_Site_Manager__c,
 Medicaid_Calendar_ID__c,
 Medicare_Calendar_ID__c,
 PR_Calendar_ID__c
 From Site__c
 where Name in :sName ];
//Loop to directly relate the Site__c.Id to the OwnerId of the record
 for(user u : ulist){
 for(Site__c s:lSite){
 if(lName.get(u.id) == s.Name){
 uSiteNames.put(u.id,s);
 }
 }
 uDivisions.put(u.id, u.Division);
 }
//Use the Account.Plan_Site__c to obtain site id
 Map<Id, Site__c> siteMap;
 if (siteIDs.size() > 0) {
 //obtain the site record based on Account.Plan_Site
 siteMap = new Map<Id, Site__c>([select Id,
 Medicaid_Site_Manager__c,
 Medicare_Site_Manager__c,
 PR_Site_Manager__c,
 Medicaid_Calendar_ID__c,
 Medicare_Calendar_ID__c,
 PR_Calendar_ID__c
 From Site__c where Id in :siteIDs]);
 }
 //Create Map for ZipCode
 Map<String,State__c> ZipCodeMap;
 if(ZipCodeNames.size()>0){
 ZipCodeMap = New Map<String,State__c>([Select
 ID,
 NAME,
 CITY_NM__C,
 COUNTY_CD__C,
 COUNTY_NM__C,
 STATE_CD__C,
 BOROUGH__C
 From State__c where Name in : ZipCodeNames]);
 }
 
 if (Trigger.isInsert ||Trigger.isUpdate ){
 for (Account act : Trigger.new)
 {
 if (RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care' && act.No_Address__c==True && (act.PERSONMAILINGStreet!=Null||act.PERSONMAILINGPOSTALCODE!=Null || act.PERSONMAILINGCITY!=Null ||act.PERSONMAILINGSTATE!=Null ||act.PERSONMAILINGCOUNTRY!=Null ||act.PLan_State__c!=null||act.County__c!=null))
 {
 act.No_Address__c.addError('You are not allowed to have a Partial Address Please Uncheck and Complete Address or Remove Values from Address Fields.');
 }
 if ((RTypeMap.get(act.RecordTypeID).Name=='Member Read-Only Care' && act.No_Address__c!=True) && (act.PERSONMAILINGStreet==Null||act.PERSONMAILINGPOSTALCODE==Null))
 {
 act.No_Address__c.addError('You are not allowed to have a Partial Address. Please Complete Mailing Street and or Enter the Proper MAILING POSTALCODE to complete the Address or Remove Values from Address Fields and Check No Address.');
 }
 
 State__c[] stateInfos = [select Id, Name, County_CD__c, County_NM__c, State_CD__c, City_NM__c
 from State__c
 where Name=:act.BILLINGPOSTALCODE
 or Name=:act.PERSONMAILINGPOSTALCODE
 limit 1
 ];
 if (act.ISPERSONACCOUNT!=True)
 {
 if (act.BILLINGPOSTALCODE != null && !stateInfos.isEmpty()) {
 act.BILLINGCITY=ZipCodeMap.get([select Id from State__c Where Name = :act.BILLINGPOSTALCODE].Id).CITY_NM__C;
 act.BILLINGSTATE=ZipCodeMap.get([select Id from State__c Where Name = :act.BILLINGPOSTALCODE].Id).STATE_CD__C;
 act.BILLINGCOUNTRY='USA';
 act.County__c=ZipCodeMap.get([select Id from State__c Where Name = :act.BILLINGPOSTALCODE].Id).COUNTY_NM__C;
 act.COUNTY_CODE_3__C=ZipCodeMap.get([select Id from State__c Where Name = :act.BILLINGPOSTALCODE].Id).COUNTY_CD__C;
 act.BOROUGH__C=ZipCodeMap.get([select Id from State__c Where Name = :act.BILLINGPOSTALCODE].Id).BOROUGH__C;
 act.Plan_state__c=ZipCodeMap.get([select Id from State__c Where Name = :act.BILLINGPOSTALCODE].Id).STATE_CD__C;
 } else {
 if(act.BILLINGPOSTALCODE != null && stateInfos.isEmpty()){
 act.BILLINGPOSTALCODE.addError('Please Enter the Proper Billing PostalCode for this Account.');
 }
 }
 } else {
 if (act.PERSONMAILINGPOSTALCODE != null && !stateInfos.isEmpty()) {
 act.PERSONMAILINGCITY=ZipCodeMap.get([select Id from State__c Where Name = :act.PERSONMAILINGPOSTALCODE].Id).CITY_NM__C;
 act.PERSONMAILINGSTATE=ZipCodeMap.get([select Id from State__c Where Name = :act.PERSONMAILINGPOSTALCODE].Id).STATE_CD__C;
 act.PERSONMAILINGCOUNTRY='USA';
 act.County__c=ZipCodeMap.get([select Id from State__c Where Name = :act.PERSONMAILINGPOSTALCODE].Id).COUNTY_NM__C;
 act.COUNTY_CODE_3__C=ZipCodeMap.get([select Id from State__c Where Name = :act.PERSONMAILINGPOSTALCODE].Id).COUNTY_CD__C;
 act.BOROUGH__C=ZipCodeMap.get([select Id from State__c Where Name = :act.PERSONMAILINGPOSTALCODE].Id).BOROUGH__C;
 act.Plan_state__c=ZipCodeMap.get([select Id from State__c Where Name = :act.PERSONMAILINGPOSTALCODE].Id).STATE_CD__C;
 } else {
 if(RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care' && act.No_Address__c!=True){
 act.PERSONMAILINGPOSTALCODE.addError('Please Enter the Proper MAILING POSTALCODE for this Account. Or Check No Address');
 }
 }
 
 }
 act.Owner_Division__c = uDivisions.get(act.Ownerid);
 if (RTypeMap.get(act.RecordTypeID).Name!= 'Member Read-Only Care' )
 {
 if (uSiteNames.get(act.OwnerId)!= null) {
 act.Plan_Site__c = uSiteNames.get(act.OwnerId).Id;
 } else {
 act.Plan_Site__c.addError('There is not a default Site linked to the User record. Please select an Event Site in the Account below or ask your administrator to assign a default site.');
 }
 
 } else if (RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care'){
 if (act.Plan_State__c=='NJ' && act.County__c=='Bergen') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Essex') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Hudson') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Middlesex') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Monmouth') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Ocean') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Union') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Bronx') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Kings') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='New York') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Queens') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Richmond') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Davidson') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Maury') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Rutherford') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Williamson') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Denton') {act.Plan_site__c='a0980000006IB3nAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Tarrant') {act.Plan_site__c='a0980000006IB3nAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Bexar') {act.Plan_site__c='a0980000006IB3sAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Harris') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Brazoria') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Fort Bend') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Montgomery') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Bernalillo') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Otero') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Sandoval') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Santa Fe') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Socorro') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Torrance') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Valencia') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Hillsborough') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Orange') {act.Plan_site__c='a0980000006IB0EAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Pasco') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Pinellas') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Polk') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Broward') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Miami-Dade') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Palm Beach') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Anne Arundel') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Baltimore City') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Baltimore') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Carroll') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Harford') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Howard') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Montgomery') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Prince George') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='GA' && act.County__c=='Chatham') {act.Plan_site__c='a0980000006IB1bAAG';}
 else if (act.Plan_State__c=='GA' && act.County__c=='Fulton') {act.Plan_site__c='a0980000006IB0iAAG';}
 else{if (uSiteNames.get(act.OwnerId)!= null)
 {
 act.Plan_Site__c = uSiteNames.get(act.OwnerId).Id;
 } else {
 act.Plan_Site__c.addError('There is not a default Site linked to the User record. Please select an Event Site in the Account below or ask your administrator to assign a default site.');
 }
 }
 }
 System.debug(act);
 }
 }
}
MJ09MJ09

Your query for State__c is inside a FOR loop, so it's going to be executed for every Account that you're inserting or updating.

 

Just before the FOR loop, you create a Map for ZipCode. That map is currently indexed by the State__c Id. If you create a variation of that map that's indexed by State__c Name (which, I gather is actually a postal code), then you can remove the query from your FOR loop, and replace it with some code that finds the correct entry in the Map.

 

I hope that helps--

JayMoiahJayMoiah

Solution was reached, Thank you all for your input both here and on Dreamforce Chatter

I was able to resolve it by moving a few of the queries around and then Adding a Nullpointer caputer for validating the Zip.

Code ( Appologize on the format had to shrink it):

trigger Account_Set_Plan_Site on Account (before insert, before update)
{
//Loop to collect all the Account id and store in a set/list
 Set<ID> actOwnerIDs = new Set<ID>();
 Set<ID> siteIDs = new Set<ID>();
 Set<String> ZipCodeNames = new set<String>();
 Set<ID> RTIds = new set<ID>();
 Integer I=0;
 //Get all the Account Owners and Site Ids if Pla Site is Filled
 for (Account acct : Trigger.new)
 {
 ActOwnerIds.add(acct.OwnerID);
 RTIds.add(acct.RecordtypeId);
 if (acct.Plan_Site__c != null ) {
 siteIDs.add(acct.Plan_Site__c);
 }
 if (Acct.ISPERSONACCOUNT!=True)
 {
 if (Acct.BILLINGPOSTALCODE!= null ) {
 ZipCodeNames.add(Acct.BILLINGPOSTALCODE);
 }
 } else {
 if (Acct.PERSONMAILINGPOSTALCODE!= null ) {
 ZipCodeNames.add(Acct.PERSONMAILINGPOSTALCODE);
 }
 }
 }

 Map<ID,RecordType> RTypeMap;
 RTypeMap=New Map<ID,RecordType>([Select id, Name from RecordType Where Id in :RTIds]);

 
 // Collect the siteName and Division per the Account OwnerID
 set<String> sName = new set<String>(); // Set created for the passing of then Owner Site_Name__c
 List<Site__c> lSite = new List<Site__c>();
 List<User> uList = [Select Division, Site_Name__c from User where Id in:actOwnerIds];
 Map<Id, Site__c> uSiteNames = new Map<Id, Site__c> (); //Map for Site__c of the Owner based on the Site_Name__c
 Map<Id, string> uDivisions = new Map<Id, string> (); // Map for the Owner Division
 Map<id,String> lName = new Map<id,String>(); //
 
// Loop to Put the OwerID into LName and add the Text String to the Set of sName
 for(user u : ulist){
 lName.put(u.id,u.Site_Name__c);
 sName.add(u.Site_Name__c);
 }
// List base on collected Site_Name__c collected from the Owners user record Site_Name__c add to the Previous Loop
 lSite = [select Id,
 Name,
 Medicaid_Site_Manager__c,
 Medicare_Site_Manager__c,
 PR_Site_Manager__c,
 Medicaid_Calendar_ID__c,
 Medicare_Calendar_ID__c,
 PR_Calendar_ID__c
 From Site__c
 where Name in :sName ];
//Loop to directly relate the Site__c.Id to the OwnerId of the record
 for(user u : ulist){
 for(Site__c s:lSite){
 if(lName.get(u.id) == s.Name){
 uSiteNames.put(u.id,s);
 }
 }
 uDivisions.put(u.id, u.Division);
 }
//Use the Account.Plan_Site__c to obtain site id
 Map<Id, Site__c> siteMap;
 if (siteIDs.size() > 0) {
 //obtain the site record based on Account.Plan_Site
 siteMap = new Map<Id, Site__c>([select Id,
 Medicaid_Site_Manager__c,
 Medicare_Site_Manager__c,
 PR_Site_Manager__c,
 Medicaid_Calendar_ID__c,
 Medicare_Calendar_ID__c,
 PR_Calendar_ID__c
 From Site__c where Id in :siteIDs]);
 }
 List<State__c> states = [Select ID,NAME,CITY_NM__C,COUNTY_CD__C,COUNTY_NM__C,STATE_CD__C,BOROUGH__C From State__c where Name in : ZipCodeNames];
 Map<String,State__c> ZipCodeMap = new Map<String,State__c>();
 for(State__c state: states)
 {
 ZipCodeMap.put(State.Name,state);
 }

 if (Trigger.isInsert ||Trigger.isUpdate ){
 for (Account act : Trigger.new)
 {
 if (RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care' && act.No_Address__c==True && (act.PERSONMAILINGStreet!=Null||act.PERSONMAILINGPOSTALCODE!=Null || act.PERSONMAILINGCITY!=Null ||act.PERSONMAILINGSTATE!=Null ||act.PERSONMAILINGCOUNTRY!=Null ||act.PLan_State__c!=null||act.County__c!=null))
 {
 act.No_Address__c.addError('You are not allowed to have a Partial Address Please Uncheck and Complete Address or Remove Values from Address Fields.');
 }
 if ((RTypeMap.get(act.RecordTypeID).Name=='Member Read-Only Care' && act.No_Address__c!=True) && (act.PERSONMAILINGStreet==Null||act.PERSONMAILINGPOSTALCODE==Null))
 {
 act.No_Address__c.addError('You are not allowed to have a Partial Address. Please Complete Mailing Street and or Enter the Proper MAILING POSTALCODE to complete the Address or Remove Values from Address Fields and Check No Address.');
 }
 
 if (act.ISPERSONACCOUNT!=True)
 {
 Try {if(act.BILLINGPOSTALCODE != null ){
 I=ZipCodeMap.get(act.BILLINGPOSTALCODE).Name.length();
 }
 }catch (System.NullPointerException e) {
 }
 if (act.BILLINGPOSTALCODE != null && I>1) {
 act.BILLINGCITY=ZipCodeMap.get(act.BILLINGPOSTALCODE).CITY_NM__C;
 act.BILLINGSTATE=ZipCodeMap.get(act.BILLINGPOSTALCODE).STATE_CD__C;
 act.BILLINGCOUNTRY='USA';
 act.County__c=ZipCodeMap.get(act.BILLINGPOSTALCODE).COUNTY_NM__C;
 act.COUNTY_CODE_3__C=ZipCodeMap.get(act.BILLINGPOSTALCODE).COUNTY_CD__C;
 act.BOROUGH__C=ZipCodeMap.get(act.BILLINGPOSTALCODE).BOROUGH__C;
 act.Plan_state__c=ZipCodeMap.get(act.BILLINGPOSTALCODE).STATE_CD__C;
 } else {
 if(act.BILLINGPOSTALCODE != null && I==0){
 act.BILLINGPOSTALCODE.addError('Please Enter the Proper Billing PostalCode for this Account.');
 }
 }
 } else {
 Try {if(act.PERSONMAILINGPOSTALCODE != null ){
 I=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).Name.length();
 }
 }catch (System.NullPointerException e) {
 }
 if (act.PERSONMAILINGPOSTALCODE != null && I>1) {
 act.PERSONMAILINGCITY=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).CITY_NM__C;
 act.PERSONMAILINGSTATE=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).STATE_CD__C;
 act.PERSONMAILINGCOUNTRY='USA';
 act.County__c=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).COUNTY_NM__C;
 act.COUNTY_CODE_3__C=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).COUNTY_CD__C;
 act.BOROUGH__C=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).BOROUGH__C;
 act.Plan_state__c=ZipCodeMap.get(act.PERSONMAILINGPOSTALCODE).STATE_CD__C;
 } else {
 if(RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care' && act.No_Address__c!=True && I==0){
 act.PERSONMAILINGPOSTALCODE.addError('Please Enter the Proper MAILING POSTALCODE for this Account. Or Check No Address');
 }
 }
 
 }
 act.Owner_Division__c = uDivisions.get(act.Ownerid);
 if (RTypeMap.get(act.RecordTypeID).Name!= 'Member Read-Only Care' )
 {
 if (uSiteNames.get(act.OwnerId)!= null) {
 act.Plan_Site__c = uSiteNames.get(act.OwnerId).Id;
 } else {
 act.Plan_Site__c.addError('There is not a default Site linked to the User record. Please select an Event Site in the Account below or ask your administrator to assign a default site.');
 }
 
 } else if (RTypeMap.get(act.RecordTypeID).Name== 'Member Read-Only Care'){
 if (act.Plan_State__c=='NJ' && act.County__c=='Bergen') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Essex') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Hudson') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Middlesex') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Monmouth') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Ocean') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NJ' && act.County__c=='Union') {act.Plan_site__c='a0980000006IB1qAAG';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Bronx') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Kings') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='New York') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Queens') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='NY' && act.County__c=='Richmond') {act.Plan_site__c='a0980000006IB3JAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Davidson') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Maury') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Rutherford') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TN' && act.County__c=='Williamson') {act.Plan_site__c='a0980000006IB3YAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Denton') {act.Plan_site__c='a0980000006IB3nAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Tarrant') {act.Plan_site__c='a0980000006IB3nAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Bexar') {act.Plan_site__c='a0980000006IB3sAAG';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Harris') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Brazoria') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Fort Bend') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='TX' && act.County__c=='Montgomery') {act.Plan_site__c='a0980000006IB3FAAW';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Bernalillo') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Otero') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Sandoval') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Santa Fe') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Socorro') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Torrance') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='NM' && act.County__c=='Valencia') {act.Plan_site__c='a0980000006IB39AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Hillsborough') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Orange') {act.Plan_site__c='a0980000006IB0EAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Pasco') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Pinellas') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Polk') {act.Plan_site__c='a0980000006IB0JAAW';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Broward') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Miami-Dade') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='FL' && act.County__c=='Palm Beach') {act.Plan_site__c='a0980000006IB09AAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Anne Arundel') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Baltimore City') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Baltimore') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Carroll') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Harford') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Howard') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Montgomery') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='MD' && act.County__c=='Prince George') {act.Plan_site__c='a0980000006IB1lAAG';}
 else if (act.Plan_State__c=='GA' && act.County__c=='Chatham') {act.Plan_site__c='a0980000006IB1bAAG';}
 else if (act.Plan_State__c=='GA' && act.County__c=='Fulton') {act.Plan_site__c='a0980000006IB0iAAG';}
 else{if (uSiteNames.get(act.OwnerId)!= null)
 {
 act.Plan_Site__c = uSiteNames.get(act.OwnerId).Id;
 } else {
 act.Plan_Site__c.addError('There is not a default Site linked to the User record. Please select an Event Site in the Account below or ask your administrator to assign a default site.');
 }
 }
 }
 System.debug(act);
 }
 }
}
This was selected as the best answer