Ax Technical Blog: DELETE ACTIONS IN AX 2012

DELETE ACTIONS IN AX 2012

The DeleteAction element helps maintain database consistency when a record is deleted. Define delete actions to specify what should occur when data being deleted in the current table is related to data in another table.

Use the following best practices.

· Have a delete action on every relation between two tables.

· Use table delete actions instead of writing code to specify whether deletes are restricted or cascaded.

Types Of Delete Actions:

Cascade:

· A cascading deletion action will delete all records in the related table

· While deleting a record, first the data in the related table is deleted and then data in the Current table is deleted.

Example:-

· Suppose we have two tables (Customer & Order) and the relation is of One-To-Many i.e. Customer can have many orders

· So on a parent table i.e.(Customer) if I set a delete action property to “CASCADE” for Order table. Then If I go and delete the record from a Customer table. It will also delete all the related records in Order table automatically

Restricted

· While deleting a record, if the record having any transactions then a warning message will appear. If the record is not having any transactions in another table then only deleted.

· A restricting delete action will raise an error message if the user tries to delete a record, where records exist in the related table where the foreign key is equivalent to the primary key of the current table.

Example Of Restricted:-

Suppose we have two tables (Customer & Order) and the relation is of One-To-Many i.e Customer can have many orders

So on a parent table i.e.(Customer) if I set a delete action property to “RESTRICTED” for Order table. Then If I go and delete the record from a Customer table. It will first check the record in the child table and if exist that warning prompt saying that first we need to delete a record from child table.

Cascade Restricted:-

This delete action normally works as a restricted delete action. However if the deletion is performed through X++ code, no error will be raised and the deletion will be cascaded to the related table.

Example Of Cascade + Restricted

Suppose we have three tables (Person ,Customer & Order) Now Person is a parent of Customer table, and Customer is a parent of Order table having (One-To-Many) relations

If I set a Delete action property on Person table to “CASCADE” for customer table and If I set a Delete Action property on a CUSTOMER table for Order table to “CASCADE +RESTRICTED”.

So if I delete a record from Customer table then It will first check the record in the child table(order table) and if exist that warning prompt saying that first we need to delete a record from child table.

But if I delete a record from Person table it will automatically delete a record in Customer table and all records related to customer table in Order table would also be deleted.

Creating Delete Action:

1. In the Application Object Tree (AOT), expand the Data Dictionary.

2. Expand Tables, and then locate the table that you want to add a delete action to.

3. Click the table, right-click DeleteActions, and then click New DeleteAction.

4. Right-click the new delete action, and then click Properties.

5. Select a related table from the Table property list.

6. Set the DeleteAction property.

Cascade delete action example:-

Here I have created two tables

1. DIPL_Mah_Organisation

2. DIPL_Mah_Employ

And I created a Delete action on parent table DIPL_Mah_Organisation with delete Action Property “CASCADE”

Data in the Table DIPL_Mah_Organisation

Data in the Table DIPL_Mah_Employ

So on a parent table i.e.( DIPL_Mah_Organisation) if I set a delete action property to “CASCADE” for DIPL_Mah_Employ. Then If I go and delete the record from a DIPL_Mah_Organisation. It will also delete all the related records in DIPL_Mah_Employ table automatically

Here am deleting the record xyz from Parent Table i.e DIPL_Mah_Organisation

Data will be deleted in both tables

Data after deletion in two tables

Data in DIPL_Mah_Organisation:-

Data in DIPL_Mah_Employ:-

Restricted delete action Example:

I created a Delete action on parent table DIPL_Mah_Organisation for DIPL_Mah_Employ table with delete Action Property “RESTRICTED”

Data in the Table DIPL_Mah_Organisation:-

Data in the Table DIPL_Mah_Employ:-

So on a parent table i.e.(DIPL_Mah_Organisation) if I set a delete action property to “RESTRICTED” for DIPL_Mah_Employ table. Then If I go and delete the record from a DIPL_Mah_Employ. It will first check the record in the child table and if exist that warning prompt saying that first we need to delete a record from child table.

Here am deleting record shiva from DIPL_Mah_Organisation.

It throwing error like :-

Now am deleting the same record “shiva” which I tried to delete before from DIPL_Mah_Organisation table,

But this time am deleting that record from child table i.e DIPL_Mah_Employ table.

This time record deleted successfully.

DIPL_Mah_Employ table data after the delete action:

But the record is not deleted from Parent Table i.e DIPL_Mah_Organisation.

Now if i tried to delete that record from parent table,record will be deleted without throwing any error

If we select yes button record will be deleted successfully

Data in DIPL_Mah_Organisation table after delete action:-

The selected record was deleted.

CASCADE + RESTRICTED EXAMPLE:-

We have three tables (DIPL_Mah_Organisation, DIPL_Mah_Employ, DIPL_Mah_EmpAddress) Now DIPL_Mah_Organisation is a parent of DIPL_Mah_Employ, and DIPL_Mah_Employ is a parent of DIPL_Mah_EmpAdress table having (One-To-Many) relations

If I set a Delete action property on DIPL_Mah_Organisation table to “CASCADE” for DIPL_Mah_Employ table and If I set a Delete Action property on a DIPL_Mah_Employ table for DIPL_Mah_EmpAddress table to “CASCADE +RESTRICTED”.

So if I delete a record from DIPL_Mah_Employ table table then It will first check the record in the child table (DIPL_Mah_EmpAddress) and if exist that warning prompt saying that first we need to delete a record from child table.

But if I delete a record from DIPL_Mah_Employ table table it will automatically delete a record in DIPL_Mah_Employ and all records related to DIPL_Mah_Employ in DIPL_Mah_EmpAddress table would also be deleted.

Before applying delete action once we checks the data available in the tables

Data in the DIPL_Mah_Organisation:-

Data in the DIPL_Mah_Employ:-

Data in DIPL_Mah_EmpAddress:-

Now am going to delete the record Sravani which is common in three tables.

Here am setting a Delete action property on DIPL_Mah_Organisation table to “CASCADE” for DIPL_Mah_Employ table

And am setting a Delete Action property on a DIPL_Mah_Employ table for DIPL_Mah_EmpAddress table to “CASCADE +RESTRICTED”.

Now if I try to delete a record from DIPL_Mah_Employ table then It will first check the record in the child table (DIPL_Mah_EmpAddress) and if exist that warning prompt saying that first we need to delete a record from child table.

Here am trying to delete the record “sravani” from table DIPL_Mah_Employ