Apex Data Manipulation Language: A data manipulation language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.
DML
Manipulating Records with DML
Create and modify records in Salesforce by using the Data Manipulation Language, abbreviated as DML. DML provides a way to manage records by providing simple statements to insert, update, merge, delete, and restore records.
This example adds the Acme account to Salesforce. An account sObject is created first and then passed as an argument to the insert statement, which persists the record in Salesforce.
Account acct = newAccount(Name=’Acme’, Phone=’(415)555-1212’,NumberOfEmployees=100); Insert acct;
DML Statements
The following DML statements are available.
- insert
- update
- upsert
- delete
- undelete
- merge
Each DML statement accepts either a single sObject or a list (or array) of sObjects. Operating on a list of sObjects is a more efficient way for processing records.
ID Field Auto-Assigned to New Records
When inserting records, the system assigns an ID foe each record. In addition to persisting the ID value in the database, the ID value is also autopopulated on the sObject variable that you used as an argument in the DML call.
Account acct = new Account(name=’Acme’, Phone=’(415)555-1212’,NumberOfEmployees=100); insert acct;
//Get the new ID on the inserted sObject argument
ID acctID = acct.id;
// Display this ID in the debug log
System.debug(‘ID = ‘ + acctID);
You can use this sObject variable to perform further DML operations, such as updates, as the system will be able to map the sObject variable to its corresponding records by matching the ID.
Bulk DML
You can perform DML operations either on a single sObject , or in bulk on a list of sObject. Performing bulk DML operations is the recommended way because it helps avoid hitting governor limits, such as the DML limit of 150 statements per Apex transaction.
Upserting Records
If you have a list containing a mix of new and existing records, you can process insertions and updates to all records in the list by using the upsert statement. Upsert helps avoid the creation of duplicate records.
The upsert statement matches the sObject with existing records by comparing values of one field. If you don’t specify a field when calling this statement, the upsert statement uses the sObject’s ID to match the sObject with existing records in Salesforce. Alternatively, you can specify a field to use for matching. For custom object, specify a custom field marked as external ID. For standard objects, you can specify any field that has the idLookup property set to true.
Upsert Syntax
upsert sObject / sObject[]
upsert sObject / sObject[] field
- If the key is not matched, a new object record is created.
- If the key is matched once, the existing object record is updated
- If the key is matched multiple times, an error is generated ant the object record is neither inserted or updated.
Deleting Records
You can delete persisted records using the delete statement. Deleted records aren’t deleted permanently from Force.com, but they’re placed in the Recycle Bin for 15 days from where they can be restored.
Contact[] contactsDel = [SELECT Id FROM Contact WHERE LastName=’Smith’];
Delete contactsDel;
DML Statement Exceptions
If a DML operation fails, it returns an exception of type DmlException. You can catch exceptions in your code to handle error conditions.
try{
// This causes an exception because
// the required Name field is not provided.
Account acct = new Account();
// Insert the account
insert acct;
} catch (DmlException e) {
System.debug(‘A DML exception ha soccurred: ‘+
e.getMessage());
}
Database Methods
Apex contains the built-in Database class, which provides methods that perform DML operations and mirror the DML statement counterparts.
These Database methods are static and are called on the class name.
- insert()
- update()
- upsert()
- delete()
- undelete()
- merge()
Unlike DML statements, Database methods have an optional allOrNone Boolean parameter that allows you to specify whether the operation should partially succeed. When this parameter is set to false, if errors occur on a partial set of records, the successful records will be committed and errors will be returned for the failed records. Also, no exceptions are thrown with the partial success option.
Database.insert(recordList, false);
The Database methods return result objects containing success or failure information for each record. For example, insert and update operations each return an array of Database.SaveResult objects.
Database.SaveResult[] results = Database.insert(recordList, false);
Upsert returns Database.UpsertResult objects, and delete returns Database.DeleteResult objects.
By default, the allOrNone parameter is true, which means that the Database method behaves like its DML statement counterpart and will thrown an exception if a failure is encountered.
Database.insert(recordList);
And:
Database.insert(recordList, true);
Example: Inserting Records with Partial Success
List<Contact> conlist = new List<Contact>
{
new Contact(FirstName=’Joe’,LastName=’Smith’,Departement=’Finance’),
new Contact(FirstName=’Kathy’,LastName=’Smith’, Department=’Technology’),
new Contact(FirstName=’Caroline’,LastName=’Roth’,Department=’Finance’),
newContact()
};
Database.SaveResult[] srList=Database.insert(conList, false);
for (Database.SaveResult sr : srList)
{
if (sr.isSuccess()){
System.debug(‘Successfully inserted contact. Contact ID: ‘+ sr.getId());
} else{
For(Database.Error err : sr.getErrors())
{
System.debug(‘The following error has occurred.’);
System.debug(err.getStatusCode() + ‘:’ + err.getMessage());
System.debug(‘Contact fields that affected this error: ‘+
Err.getFields());
}
}
}
Should You Use DML Statements or Database Methods?
- Use DML statements if you want any error that occurs during bulk DML processing to be thrown as an Apex exception that immediately interrupts control flow (by using try…catch blocks)
Working with Related Records
Create and manage records that are related to each other through relationships.
Inserting Related Records
You can insert records related to existing records if a relationship has already been defined between the two objects, such as a lookup or master-detail relationship. A record is associated with a related record through a foreign key ID. For example, if inserting a new contact, you can specify the contact’s related account record by setting the value of theAccountId field.
Account acct = new Account(Name=’SFDC Account’);
insert acct;
ID acctID = acct.ID
Contact mario = new Contact(
FirstName=’Mario’,
LastName=’Ruiz’,
Phone=’415.555.1212’,
AccountId=acctID);
insert Mario;
Updating Related Records
Fields on related records can’t be updated with the same call to the DML operation and require a separate DML call. For example, if inserting a new contact, you can specify the contact’s related account record by setting the value of theAccountId field. However, you can’t change the account’s name without updating the account itself with a separate DML call. Similarly, when updating a contact, if you also want to update the contact’s related account you must make two DML calls.
// Query for the contact, which has been associated with an account.
Contact queriedContact = [SELECT Account.Name
FROM Contact
WHERE FirstName = ‘Mario’ AND LastName=’Ruiz’
LIMIT 1];
queriedContact..Phone = ‘(415)555-1213’;
queriedContact..Account.Industry = ‘Technology’;
update queriedContact;
update queriedContact.Account;
Deleting Related Records The delete operation supports cascading deletions. If you delete a parent object, you delete its children automatically, as long as each child record can be deleted.
Account[] queriedAccounts = [ SELECT Id FROM Account WHERE Name=’SFDC Account’];
delete queriedAccounts;
About Transactions
DML operations execute within a transaction. All DML operations in a transaction either complete successfully, or if an error occurs in one operation, the entire transaction is rolled back and no data is committed to the database. The boundary of a transaction can be a trigger, a class method, an anonymous block of code, an Apex page, or a custom Web service method.
Hits: 864