Apex Data Manipulation Language

In previous lessons in this tutorial, you’ve seen what an sObject is, how to query sObjects and how to traverse relationships between sObjects. Now, you’re going to learn how to manipulate records in the database using the Apex Data Manipulation Language (DML). DML enables you to insert, update, delete or restore data in the database.

Here is an example that inserts a new invoice statement by calling insert. Try it out:

Invoice_Statement__c inv = new Invoice_Statement__c(Description__c='My new invoice');
// Insert the invoice using DML. 
    
insert inv;

After the invoice statement is inserted, the sObject variable inv will contain the ID of the new invoice statement.

Now, let’s update the invoice statement by changing its status. Execute the following code to modify the just inserted invoice statement’s status and update the record in the database.

// First get the new invoice statement 
    
Invoice_Statement__c inv = [SELECT Status__c 
                            FROM Invoice_Statement__c 
                            WHERE Description__c='My new invoice'];
// Update the status field 
    
inv.Status__c = 'Negotiating';
update inv;

We’re done with this invoice statement, so let’s delete it using the delete statement. Try this sample.

// First get the new invoice statement 
    
Invoice_Statement__c inv = [SELECT Status__c 
                            FROM Invoice_Statement__c 
                            WHERE Description__c='My new invoice'];
delete inv;

Deleting a record places it in the Recycle Bin from where you can restore it. Records in the Recycle Bin are temporarily stored for 15 days before they’re permanently deleted. To restore a record, just use the undelete DML statement. Notice that we used the ALL ROWS keywords in the SOQL query to be able to retrieve the deleted record.

Invoice_Statement__c inv = [SELECT Status__c 
                            FROM Invoice_Statement__c 
                            WHERE Description__c='My new invoice'ALL ROWS];
undelete inv;

Note

Apex supports other DML operations such as merge and upsert. For more information, see the Force.com Apex Code Developer's Guide.

Database DML Methods

Alternatively, you can perform DML operations by calling the methods provided by the Database class. The DML statements you’ve just learned also have corresponding Database methods that can be called on the Database class: Database.DMLOperation. The Database DML methods take a single sObject or a list of sObjects as their first argument. They also take a second optional Boolean argument called opt_allOrNone that specifies whether the operation allows for partial success. If set tofalse and a record fails, the remainder of the DML operation can still succeed. The Database DML methods return the results of the DML operation performed.

Here is an example that inserts two invoice statements and allows partial success. It then iterates through the DML results and gets the first error for failed records. Try it out:

Invoice_Statement__c inv1 = new Invoice_Statement__c(Description__c='My new invoice');
Invoice_Statement__c inv2 = new Invoice_Statement__c(Description__c='Another invoice');
// Insert the invoice using DML. 
    
Database.SaveResult[] lsr = Database.insert(
                            new Invoice_Statement__c[]{inv1, inv2}, false);

// Iterate through the results and  
    
//   get the first error for each failed record. 
    
for (Database.SaveResult sr:lsr){
    if(!sr.isSuccess())
        Database.Error err = sr.getErrors()[0];
}

Note

Setting the opt_allOrNone argument to false is a way to avoid getting an exception when a DML operation fails. 

After the invoice statements have been inserted, let’s delete them. This next example performs a query first to get the invoices created in the previous example and deletes them. It then iterates through the results of the delete operation and fetches the first error for failed records. Execute the following:

Invoice_Statement__c[] invs = [SELECT Id 
                              FROM Invoice_Statement__c 
                              WHERE Description__c='My new invoice'
                              OR Description__c='Another invoice'];
// Delete the invoices returned by the query. 
    
Database.DeleteResult[] drl = Database.delete(invs, false);

// Iterate through the results and  
    
//   get the first error for each failed record. 
    
for (Database.DeleteResult dr:drl){
    if(!dr.isSuccess())
        Database.Error err = dr.getErrors()[0];
}

As you’ve seen in the previous section, deleted records are placed in the Recycle Bin for 15 days. In this example, we’ll restore the records we just deleted by callingDatabase.undelete. Notice that we used the ALL ROWS keywords in the SOQL query to be able to retrieve the deleted records.

Invoice_Statement__c[] invs = [SELECT Status__c 
                            FROM Invoice_Statement__c 
                            WHERE Description__c='My new invoice'
                            OR Description__c='Another invoice'ALL ROWS];
// Restore the deleted invoices. 
    
Database.UndeleteResult[] undelRes = Database.undelete(invs, false);

// Iterate through the results and  
    
//   get the first error for each failed record. 
    
for (Database.UndeleteResult dr:undelRes){
    if (!dr.isSuccess())
        Database.Error err = dr.getErrors()[0];
}

When to Use DML Statements and Database DML Statements

Typically, you will want to use Database methods instead of DML statements if you want to allow partial success of a bulk DML operation by setting the opt_allOrNoneargument to false. In this way, you avoid exceptions being thrown in your code and you can inspect the rejected records in the returned results to possibly retry the operation.Database methods also support exceptions if not setting the opt_allOrNoneargument to false.

Use the DML statements if you want any error during bulk DML processing to be thrown as an Apex exception that immediately interrupts control flow and can be handled using try/catch blocks. This behavior is similar to the way exceptions are handled in most database procedure languages.