I receive the following error when trying to use the data loader to insert records into the database. I am inserting data into a cross-reference table, but creating the related customer and account records if they do not already exist then inserting the record, because the cross reference table is a child of customer_data and account_data. I believe my issue comes from the for loop, but I don't know how to structure the trigger otherwise. 

Thanks for your help.

ERROR:

insertCustomerAccount: execution of BeforeInsert

caused by: System.DmlException: Upsert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, insertCustomerAccount: execution of BeforeInsert

caused by: System.DmlException: Upsert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, insertCustomerAccount: execution of BeforeInsert

caused by: System.DmlException: Upsert failed. First exception on row 0; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, insertCustomerAccount: execution of BeforeInsert

caused by: System.Exception: Too many SOQL queries: 21

Trigger.insertCustomerAccount: line 119, column 34: []

Trigger.insertCustomerAccount: line 221, column 13: []

Trigger.insertCustomerAccount: line 221, column 13: []

Trigger.insertCustomerAccount: line 221, column 13

trigger insertCustomerAccount on Customer_Account_Link__c (before insert) {

   Customer_Data__c CustID; 

   Account_Data__c AcctID;

   Customer_Account_Link__c CustAcctLinkID;

   Customer_Account_Link__c CustNum;

   Customer_Account_Link__c AcctNum;

   integer custMatch;

   integer acctMatch;

   string customerID;
   
   string customerNum;   

   string accountID;

   Database.upsertResult CustResult;

   Database.upsertResult AcctResult;

   

   // Account_Data__c AcctID; 

   

   for(Customer_Account_Link__c custAcctLink :Trigger.new) {

            //Check to see if a customer record already exists 

            custMatch = [SELECT count() 

                        FROM Customer_Data__c c

                        WHERE c.X_CUSTOMER_SOURCE_REF_ID__c =:custAcctLink.Customer_id__c];

                        

            // Get Customer ID if it exists

            

            if(custMatch > 0) {

                        CustID = [SELECT c.id, c.X_CUSTOMER_SOURCE_REF_ID__C 

                        FROM Customer_Data__c c

                        WHERE c.X_CUSTOMER_SOURCE_REF_ID__c=:custAcctLink.Customer_id__c 

                        LIMIT 1];

                        customerID = CustID.Id;

                        customerNum = CustID.X_CUSTOMER_SOURCE_REF_ID__C;                        

            } else {

                        CustId = new Customer_Data__c();

                        CustId.Account_Number__c = custAcctLink.account_number__c;

                        CustId.Address_1__c = custAcctLink.Address_Line_1__c;

                        CustId.Address_2__c = custAcctLink.Address_Line_2__c;

                        CustId.City__c = custAcctLink.City__c;

                        CustId.State__c = custAcctLink.State__c; 

                        CustId.X_Postal_Code__c = custAcctLink.Postal_Code__c; 

                        CustId.Last_Updated_Timestamp__c = custAcctLink.Last_Updated_Timestamp__c; 

                        CustId.Home_Phone__c = custAcctLink.Home_Phone__c; 

                        CustId.Work_Phone__c = custAcctLink.Work_Phone__c;

                        CustId.Mobile_Phone__c = custAcctLink.Mobile_Phone__c;

                        CustId.Fax__c = custAcctLink.Fax__c;

                        CustId.Taxpayer_Identification_Number__c = custAcctLink.Taxpayer_Identification_Number__c; 

                        CustId.Fax__c = custAcctLink.Fax__c; 

                        CustId.X_CUSTOMER_SOURCE_REF_ID__C = custAcctLink.Customer_id__c; 

                        CustResult = Database.upsert(CustId);

                        customerID = CustResult.Id;
                        
                        customerNum = CustId.X_CUSTOMER_SOURCE_REF_ID__C;                        

            }

            

            //Check to see whether the account record exists

            acctMatch = [SELECT count() 

            FROM Account_Data__c c

            WHERE c.X_ACCOUNT_SOURCE_REF_ID__c =:custAcctLink.Account_Number__c];

            //If account exists, obtain the ID from the record with the matching account number.            

            if(acctMatch > 0) 

            {

                        AcctId = [SELECT a.id 

                        FROM Account_Data__c a

                        WHERE a.X_ACCOUNT_SOURCE_REF_ID__c=:custAcctLink.ACCOUNT_NUMBER__c 

                        LIMIT 1]; 

                        accountID = AcctId.Id;

            } else {

                        //Otherwise insert the account record and get the account id                     



                        AcctId = new Account_Data__c();
                        
                        if(custMatch > 0) 

                        {

                                    AcctId.Customer__c = customerID;

                                    AcctId.CustomerID__c = customerNum; 

                       } 

                       else {

                                    AcctId.Customer__c = CustResult.id;

                                    AcctId.CustomerID__c = CustId.X_CUSTOMER_SOURCE_REF_ID__C; 

                        }                         

                        AcctId.X_ACCOUNT_SOURCE_REF_ID__c = custAcctLink.account_number__c; 

                        AcctId.Status__c = custAcctLink.Account_Status__c;

                        // AcctId.Account_Open_Date__c = custAcctLink.Date_Established__c; 

                        AcctResult = Database.upsert(AcctId);

                        accountID = AcctResult.Id;

            }

            // Upsert Customer Account Link Record

            // TODO: Parse out field into MM/DD/YYYY for 

            string year = '2010';

            string month = '04';

            string day = '01';

            string establishDate = (year + '-' + month + '-' + day);

            Customer_Account_Link__c uploadCAL = new Customer_Account_Link__c ();

            

            uploadCAL.Customer__c = customerID;
            
            uploadCAL.Customer_ID__c = customerNum;

            uploadCAL.Account__c = accountID;

            

            uploadCAL.Account_Number__c = custAcctLink.account_number__c;

            uploadCAL.Account_Status__c = custAcctLink.account_status__c;

            uploadCAL.Address_Line_1__c = custAcctLink.Address_Line_1__c;

            uploadCAL.Address_Line_2__c = custAcctLink.Address_Line_2__c;

            uploadCAL.City__c = custAcctLink.City__c;

            uploadCAL.State__c = custAcctLink.State__c; 

            uploadCAL.Postal_Code__c = custAcctLink.Postal_Code__c; 

            uploadCAL.Date_Established__c = custAcctLink.Date_Established__c;

            uploadCAL.Last_Updated_Timestamp__c = custAcctLink.Last_Updated_Timestamp__c; 

            uploadCAL.Home_Phone__c = custAcctLink.Home_Phone__c; 

            uploadCAL.Work_Phone__c = custAcctLink.Work_Phone__c;

            uploadCAL.Mobile_Phone__c = custAcctLink.Mobile_Phone__c;

            uploadCAL.Fax__c = custAcctLink.Fax__c;

            uploadCAL.Taxpayer_Identification_Number__c = custAcctLink.Taxpayer_Identification_Number__c; 

            uploadCAL.Fax__c = custAcctLink.Fax__c;

            Database.upsert(uploadCAL);

   }

}