+ Start a Discussion
DDSDDS 

Apex Trigger: Too many DML statements: 21

I am receiving the error:

 

Developer script exception from Company : CreateIndividualAccount : CreateIndividualAccount: execution of BeforeInsert caused by: System.Exception: Too many DML statements: 21

 

and I understand why but I'm just having a hard time figuring out how to fix it.

 
The way the trigger works, it checks if the contact has an account, if it

doesnt it creates an account for it and associates the contact to that new
account. The problem is that it's doing this for each account everytime instead
of grouping all these accounts in a list and then updating all the contacts with
the new accounts in bulk. I'm not a versed enough programmer to 

 

 

trigger CreateIndividualAccount on Contact (before insert, before update) {
RecordType arec = [SELECT ID from RecordType WHERE SobjectType='Account' AND Name='Individual' limit 1];
for (Contact c : trigger.new)
{
if(c.AccountID == null)
{
Account[] acc = new Account[0];
string cName = (c.FirstName + ' ' + c.LastName);
acc.add(new Account (Name = cName, RecordTypeId = arec.Id));

insert acc;
c.AccountID = acc[0].Id;
}
}
}

 

  

Is there a way to go arround this?

 

 

Thanks in advance for you help.

bob_buzzardbob_buzzard

As you have correctly surmised, you need to move your insert outside the loop.

 

I think the following should do the trick:

 

Create two lists - one containing the contacts without accounts and the other containing the newly created account.

 

Iterate your trigger.new as you are now, and insert into these lists as appropriate.

 

Then carry out an insert on the new account list, which I believe will add an id to each element.

 

Then iterate the new account list and, for each element, update the accountID field of the corresponding contact.

 

Of course there will be other ways around this, but this is the first that springs to mind. 

DDSDDS
trigger CreateIndividualAccount on Contact (before insert, before update) {
RecordType arec = [SELECT ID from RecordType WHERE SobjectType='Account' AND Name='Individual' limit 1];
Account[] acc = new Account[0];
Contact[] ct = new Contact[0];

for (Contact c : trigger.new)
{
if(c.AccountID == null)
{
string cName = (c.FirstName + ' ' + c.LastName);
acc.add(new Account (Name = cName, RecordTypeId = arec.Id));

}
insert acc;
ct.add ???? //(I really don't know what to do at this point. How do I add the list of accounts to the existing list of contacts?)
}
}
DDSDDS

bob, as I was trying to type, i'm
not a very experienced programmer. This was just a trigger I tried to create to
overcome an annoyance. I will still try to translate what you just wrote into
code but i'm not clear on a couple of things.



1 - Won't an account only have a
salesforce ID if it was actually "inserted"?



2 - How do I add a list (new
accounts) to a list (contacts)? What would be the syntax? 

 

trigger CreateIndividualAccount on Contact (before insert, before update) {
RecordType arec = [SELECT ID from RecordType WHERE SobjectType='Account' AND Name='Individual' limit 1];
Account[] acc = new Account[0];
Contact[] ct = new Contact[0];

for (Contact c : trigger.new)
{
if(c.AccountID == null)
{
string cName = (c.FirstName + ' ' + c.LastName);
acc.add(new Account (Name = cName, RecordTypeId = arec.Id));

}
insert acc;
ct.add ???? //(I really don't know what to do at this point. How do I add the list of accounts to the existing list of contacts?)
}
}

bob_buzzardbob_buzzard

Once you have carried out the insert on the array of accounts, I believe the records will be updated with their id.

 

 

You were almost there.  I've added what I think is the remaining code - uncompiled, so may be the odd typo:

 

 

trigger CreateIndividualAccount on Contact (before insert, before update) {
RecordType arec = [SELECT ID from RecordType WHERE SobjectType='Account' AND Name='Individual' limit 1];
Account[] acc = new Account[0];
Contact[] ct = new Contact[0];

for (Contact c : trigger.new)
{
if(c.AccountID == null)
{
string cName = (c.FirstName + ' ' + c.LastName);
acc.add(new Account (Name = cName, RecordTypeId = arec.Id));
// add the corresponding contact to the ct list
ct.add(c);

}
insert acc;

// iterate the lists - they are both the same size, so we can use the size of either

for (Integer count=0; count<acc.size(); count++)
{
Contact cont=ct.get(count);
ct.AccountId=acc.get(count).id;
}
}
}

 

 

 

 

 

 

 

Message Edited by bob_buzzard on 01-14-2010 03:40 AM
DDSDDS

bob, thanks for all your help

 

However, when you add the accounts to the list it doesnt create an Id and it returns this error:

 

CreateIndividualAccount: execution of BeforeInsert caused by: System.DmlException: Insert failed. First exception on row 0 with id 001A00000059l9sIAA; first error: INVALID_FIELD_FOR_INSERT_UPDATE, cannot specify Id in an insert call: [Id] Trigger.CreateIndividualAccount: line 16, column 1

 It doesnt seem to be a way around this issue... The trigger works for individual records but I'll just have to tell everyone to add a column in their excel file with an account name (=A3&" "&A4) when their doing bulk Inserts.

 

Again, I really appreciate your help and don't hesitate to post a solution if you come up with one :)