Insert, Modify, ModifyAll, Delete, DeleteAll, and Truncate methods

The following methods maintain the database by adding, modifying, and removing records:

• Insert
• Modify
• ModifyAll
• Delete
• DeleteAll
• Truncate

These methods are some of the most frequently used AL methods.

Some of these methods return an optional Boolean value that indicates whether the method succeeded. If you don't handle the return value in your code, a runtime error occurs when a method returns false. If you handle the return value by testing its value in an if statement, no error occurs, and you must take corrective action in the code.

Insert method

The Insert method inserts a record in a table. Insert has the following syntax.

[Ok := ]  Record.Insert([RunTrigger: Boolean[, InsertWithSystemId: Boolean]])

A record must be assigned a SystemId. You can assign your own value or have the platform assign an autogenerated value. The following example inserts a new record, with the SystemId, No., and Name fields specified in the assigned values, while other fields have their default values. If the No. field is the primary key of the Customer table, then the record is inserted in the Customer table unless the table already contains a record with the same primary key. In this case, you receive an error message because the return value isn't tested.

var
    Customer: Record Customer;
begin
    Customer.Init;
    Customer.SystemId := '{B6666666-F5A2-E911-8180-001DD8B7338E}';  
    Customer."No." := '4711';  
    Customer.Name := 'Andrew Dixon';  
    Customer.Insert(false, true);
end; 

Modify method

Modify modifies a record that already exists. Learn more in Modify method. Modify has the following syntax:

[Ok :=] Record.Modify([RunTrigger])  

Modify returns an optional Boolean value. It returns true if the record to be modified exists; otherwise, it returns false.

The following example changes the name of customer 4711 to Richard Roe. This example requires that you create the following variable.

Customer.Get('4711');  
Customer.Name := 'Richard Roe';  
Customer.Modify;  

ModifyAll method

ModifyAll performs a bulk update of records. Learn more in ModifyAll method. ModifyAll has the following syntax:

Record.ModifyAll(Field, NewValue [, RunTrigger])  

ModifyAll uses the current filters. This means that you can perform the update on a specified set of records in a table. ModifyAll returns no value, nor does it cause an error if the set of records to be changed is empty.

In the following example, the SetRange statement selects the records where Salesperson Code is PS. The ModifyAll statement changes the Salesperson Code of these records to JR. The example requires that you create the following variable.

Customer.SetRange("Salesperson Code",'PS','PS');  
Customer.ModifyAll("Salesperson Code",'JR');  

Delete method

Delete deletes a record from the database. Learn more in Delete method. Delete has the following syntax.

[Ok :=] Record.Delete([RunTrigger])  

The record that you want to delete must be specified by using the values in the primary key fields before you call this method. Delete does take filters into consideration.

The following example shows how to use Delete to delete the record for customer number 4711. The following example requires that you create the following variable.

Customer."No." := '4711';  
Customer.Delete;  

Delete returns an optional Boolean value. It returns true if the record could be found; otherwise, it returns false. Unless you test this value in your code, a run-time error occurs when Delete fails.

When you're developing your own applications, you should consider the following scenario:

1. Retrieve a record from the database.
2. Perform various checks to determine whether the record should be deleted.
3. Delete the record if step 2 indicated that you should.

This scenario can cause problems in a multi-user environment. Another user can modify or delete the same record between your performing steps 2 and 3. If the record is modified, then perhaps the new contents of the record could change your decision to delete it. If another user deletes it, you can get a run-time error if you verified that the record existed (in step 1). If the design of your application indicates that you can encounter this problem, you should consider using the LockTable method. LockTable should be used sparingly because this method degrades performance. Learn more about the LockTable method in LockTable method.

DeleteAll method

DeleteAll deletes all the records that are specified by the filter settings. If no filters are applied, it deletes all the records in the table. Learn more in DeleteAll method. DeleteAll has the following syntax:

Record.DeleteAll([RunTrigger])  

The following example deletes all the records from the Customer table where the Salesperson Code is PS. This example requires that you create the following variable.

Customer.SetRange("Salesperson Code", 'PS', 'PS');  
Customer.DeleteAll;  

Truncate method

Like Delete, the Truncate method also deletes records from a table. However, the Truncate method provides a high-performance way to remove large volumes of rows from a table by skipping row‑by‑row deletions. Instead of iterating and deleting each row, the platform uses a bulk operation that significantly reduces execution time and logging overhead. The method offers options to either reset AutoIncrement values to 0 or preserve their previous values. It also validates delete permissions before running.

Truncate has the following syntax:

[Ok := ]  Record.Truncate([ResetAutoIncrement: Boolean])

Truncate returns false when truncate isn't supported.

If you supply filters, the platform copies the rows you want to keep to a temporary table, truncates the original table, and then moves the kept rows back. This process maintains the speed benefits of bulk deletion while allowing filtered removals.

The following example deletes all the records from the MyTable table where the Location Code is Red. This example requires that you create the following variable.

MyRec.SetRange("Location Code", 'Red');
Ok :=  MyRec.Truncate(true)

When to use Truncate or DeleteAll

Use Truncate when you need to clear or reduce most of a table. Example scenarios include cleanup, reset between tests, or bulk archival workflows. DeleteAll (removing every row when no filters are applied) is simpler but typically slower than Truncate for large tables, because it might still trigger per-row operations such as triggers or constraints. Use DeleteAll when not deleting a majority of the table or if the table doesn't support Truncate.

Truncate isn't supported in the following cases:

• Temporary tables, system tables, and tables of type other than Normal.
• Running inside try functions.
• Tables that have a security filter applied.
• When the current filters contain flow fields, or use a high number of marked records.
• When there are event subscribers for the OnAfterDelete or OnBeforeDelete triggers of the table.
• Tables with media fields.

Truncate is supported when the global DatabaseDelete trigger is implemented (which is different for normal DeleteAll in bulk), meaning that trigger doesn't get called on Truncate.

You can use the return value of Truncate to attempt high-performance deletion, and if it isn't supported, fall back to using DeleteAll. For example:

If not MyRec.Truncate(true) then
  MyRec.DeleteAll(false);

Related information

RecordRef.Truncate([Boolean]) method
RecordRef.Modify([Boolean]) method
RecordRef.Delete([Boolean]) method
RecordRef.DeleteAll([Boolean]) method
AL methods
AL Language reference overview
SystemId field