This is it, The Final Chapter. In this series we’ve come a long way learning how to be successful while developing back end code in Salesforce. We’ve gained knowledge about how to retrieve Data with SOQL and SOSL, and we can modify our records in memory using a looping mechanism while keeping our data secure with Access Control. Now it’s time to persist our newly created or modified records to the database. Ready?
Learning to Speak DML
Wait, what’s DML? DML stands for Data Manipulation Language. It’s a set of commands that tells the platform to perform an action in the Database. For example, the most frequently used commands are: Insert, Update, and Delete. They are very self-explanatory.
There are two other commands you can use:
- Upsert is a combination of the update and insert commands (Get it? Up..sert?). It works as follows: If the records you are working on have an ID (or an external ID) that already exists in your database, the record will be updated. If the system is unable to find a match, then the record is inserted.
- Undelete allows you to restore previously-deleted records. This is the same command that is used when you recover a record out of the recycle bin.
The 5 DML Secrets any
Wizard Developer should know
- DML statements can operate on a single record, or they can operate on a group of records at a time. Know that if you insert a group of records, the platform will break it up into batches of 200. This means that if you insert a List of Accounts that contains a thousand accounts, the platform will divide it in smaller groups of 200 accounts.
- Mind the context. Each DML action has two possible contexts: before and after, EXCEPT the undelete action which only has the after context. The context refers to when the actions happens. This is important when it comes to extending functionality via triggers.
- Two operations might be faster than one. Even though the upsert command is very convenient, it is also known to be a slower method when compared to executing an insert followed by an update call.
- The power of sorting. One of the common failures when using DMLs is called a Lock Contention. This happens when two records share a reference to a third record and are updated in the same transaction. For example, inserting or updating two contacts for the same parent account in a single transaction might cause a Record Lock Contention Error, especially if you have Data Skew on the parent account. We strongly recommend sorting your records based on the parent reference; this reduces the chances of two child records being updated in separate batches and therefore reduces the chance of a Lock Contention. Ex: If you want to insert a thousand contacts using an ETL tool like Data Loader, make sure that any contacts related to the same account are organized consecutively in your list or csv file.
- Be aware of limits. There are two main per-transaction limits you MUST respect when dealing with DMLs:
- No more than 150 DMLs per transaction. This governor limit is the reason we don’t put DMLs inside of FOR Loops and we carefully craft our triggers to be bulkified.
- You can’t act on more than 10,000 records at a time. This includes not only the records you issue the action against, but also the records that are updated/created because of your action.
Show me the code!
Issuing DML statements is pretty straightforward. Here are some examples of code:
This is as simple as it gets. In Line 1 we are creating a new account record in memory, and in Line 2 we are issuing the Insert statement against the account we just created.
A more complex scenario:
Here we see the whole cycle of this series — Retrieve, Modify, Persist —
- In Line 1 we retrieve several records from the database. These records were inserted with a misspelled value on industry.
- On Lines 3-5 we iterate over the data set and modify the value in memory.
- Lastly, on Line 6 we perform an update DML statement to persist our changes in the system.
Until now we’ve been issuing DML statements to perform Database Operations. There is another way of accomplishing the same: Using the Database Class.
The Database Class provides the same DML operations as methods. For example, using the Database Class, our previous examples would look like this:
There are three key things to note:
- The Database.Update() method will return a List of type Database.SaveResult object. This list stores the result of your operation for each record contained in the list.
- The Database.Update() method takes two parameters. The first one is the list of objects you want to operate on.
- The second parameter is called the allOrNone attribute. It is a boolean value that designates whether the operation is partial or not. If you specify False, then even if the list of objects you provided contains invalid records, the records that are valid will be successfully updated. If you specify True, then all the records provided on the list must be valid in order for the operation to succeed.
Just one more thing…
If you are like me and you are a big fan of transaction control, make special note of the following methods from the Database class:
- Database.setSavePoint() – returns a savepoint variable that holds the current state of your database; use it before issuing any DML operations.
- Database.rollBack() – if something went wrong, use this method along with the savepoint variable to roll back any DML operations you previously issued.
That’s it! You now have two great tools in your developer tool belt: DML statements and the Database Class. Use them wisely, and happy coding!
About the Author:
Clara Perez is a Salesforce MVP and Lead Developer at Great Wave who loves teaching Salesforce concepts.
Tags: DML, transaction control, database rollback, database savepoint, governor limits, save results.