Lesson 5: 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.
Lesson 5: Apex Data Manipulation Language Chapter 2: Apex Language Fundamentals
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 to false 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.
You’ll learn more about exceptions in Tutorial #9: Exception Handling.
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 calling Database.undelete. Notice that we used the ALL ROWS keywords in the SOQL query to be able to retrieve the deleted records.
Lesson 5: Apex Data Manipulation Language Chapter 2: Apex Language Fundamentals
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_allOrNone argument 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. (You’ll learn about exceptions in the next tutorial:
Tutorial #9: Exception Handling.) Database methods also support exceptions if not setting the opt_allOrNone argument 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.
Summary
In this tutorial, you learned about sObjects and how to write queries to extract information from the database. You also learned how to use Apex DML to perform insert, update, detele and restore operations.
Tell Me More...
Here are some additional resources to explore.
Rolling Back Transactions and Savepoints
Apex supports rolling back transactions. You can generate a savepoint which sets a point in the request that corresponds to a state in the database. Any DML statement that occurs after the savepoint can be discarded and the database can be restored to the same initial condition. See Tutorial #10: Executing Data Operations as a Single Transaction in Chapter 3 of this workbook to learn more about Apex transactions.
Locking Statements
Apex allows you to lock an sObject record to prevent other code from making changes to it. Use the FOR UPDATE SOQL statement to lock a record.
sObject Describes
Apex provides methods to perform describes of sObjects. You can obtain a list of all sObjects, as well as a list of fields for an sObject and field attributes. For more information, see the Force.com Apex Code Developer's Guide.
Tutorial #9: Exception Handling
In this tutorial, you’ll learn about exceptions in Apex and how to handle exceptions in your code. Also, you’ll get an overview of built-in exceptions, and you’ll create and throw your own exceptions.
Use the Developer Console to execute all of the examples in this tutorial.
Summary Chapter 2: Apex Language Fundamentals