Data Operations in X++ on Base and Derived Tables

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

In Microsoft Dynamics AX, when you insert a row into a derived table, the system automatically inserts the required base table row. When you delete a row from a base table, the system automatically deletes dependent rows from the derived table. If a problem prevents any one of these inserts or deletes from succeeding, the system rolls back the entire transaction, so that the data remains unchanged.

Background for this Walkthrough

The following topics provide background for this topic:

• Walkthrough: Creating Base and Derived Tables

• Data Selection and Manipulation

Structure of the Demonstration Tables

The code examples in this walkthrough refer to the following two tables, TabPet and TabPetDog.

TabPet Base Table

The following table shows the fields of the TabPet base table.

Field name	Data type	Mandatory	Comments
InstanceRelationTypeId	int64	No	The system requires that you add a mandatory int64 field to each base table. You can name the field anything, but you must assign the field to the InstanceRelationType property on the base table.
PetBirthDate	date	Yes	Inserts into the derived table can specify a value for this field in the base table. Specifying a value prevents the system from leaving the field value empty when it automatically inserts a row into this base table.

TabPetDog Derived Table

The following table shows the fields of the TabPetDog derived table.

Field name	Data type	Mandatory	Comments
NumberOfTeeth	int	Yes	No special fields are required on the derived table.

Insert Cascade from Derived to Base

This section describes a code example that contains insert statements for the derived table. These statements do not mention the base table, but the system automatically inserts the necessary base row into the base table. Each row in the derived table is dependent on a corresponding row in the base table.

The code example also shows a select statement that reads from the base table. Some of the data that was inserted by using a buffer variable for the derived table can be read from the base table.

Call Patterns in the Code Example

The following table shows the major statements that are made in the code example.

X++ statement	Comment
delete_from tPet;	Deletes all the rows from the base table in one operation. This delete cascades to delete all the rows in the derived table TabPetDog.
tDog.PetBirthDate = 20\07\1988;	In the AOT, the field PetBirthDate is defined on the TabPet table, not on the TabPetDog table. The field is inherited by the TabPetDog table, even though physically it exists on only the TabPet table in the underlying database.
tDog.insert();	This insert method adds a row to the base table and to the derived table that is referenced by the tDog variable.
while select * from tPet   { anyt = tPet.getFieldValue("PetBirthDate"); }	These statements show that the PetBirthDate field can be read from the base table. The AOT shows this field is defined on the TabPet base table.
while select * from tDog   { anyt = tDog.getFieldValue("PetBirthDate"); }	These statements show that the PetBirthDate field can be read from the TabPetDog derived table. The AOT shows this field is defined on the TabPet base table.

The following code example shows that an insert into the derived table automatically also inserts into the base table. The system rejects the entire transaction if either insert fails.

    static void InsertCascadeMandatoryIssueJob3I(Args _args)
    {
        TabPet tPet;
        TabPetDog tDog;
        anytype anyt;
        date dPetBirthDate;
        str sPetBirthDate, sNumberOfTeeth;
        int nNumberOfTeeth;
    
     // Local function.
     str Date2StrEasyLocal(date _date)
     {
            return ( date2Str
                (_date,
                321, // YMD
                DateDay::Digits2,
                DateSeparator::Hyphen,
                DateMonth::Digits2,
                DateSeparator::Hyphen,
                DateYear::Digits4) );
     }
    
        // Ensure both tables start empty.
        //This delete cascades to delete all rows from tDog also.
        delete_from tPet;
    
        // Inserts into the derived table,
        // which cascade to the base table.
        tDog.PetBirthDate = 20\07\1988;
        tDog.NumberOfTeeth = 42;
        tDog.insert();
    
        tDog.PetBirthDate = 29\02\2008;
        tDog.NumberOfTeeth = 39;
        tDog.insert();
    
        info("--- List base table rows after the inserts.");
        while select * from tPet
        {
            anyt = tPet.getFieldValue("PetBirthDate");
            dPetBirthDate = any2Date(anyt);
            sPetBirthDate = Date2StrEasyLocal(dPetBirthDate);
    
            info("After insert, base table variable has: " + sPetBirthDate);
        }
        info("--- List derived table rows after the inserts.");
        while select * from tDog
        {
            anyt = tDog.getFieldValue("PetBirthDate");
            dPetBirthDate = any2Date(anyt);
            sPetBirthDate = Date2StrEasyLocal(dPetBirthDate);
    
            anyt = tDog.getFieldValue("NumberOfTeeth");
            nNumberOfTeeth = any2Int(anyt);
            sNumberOfTeeth = int2Str(nNumberOfTeeth);
    
            info("After insert, the derived table variable has: "
                + sPetBirthDate + " , " + sNumberOfTeeth);
        }
    }

The following output to the Infolog is generated by the previous program.

Message (06:22:14 pm)

--- List base table rows after the inserts.

After insert, base table variable has: 1988-07-20

After insert, base table variable has: 2008-02-29

--- List derived table rows after the inserts.

After insert, the derived table variable has: 1988-07-20 , 42

After insert, the derived table variable has: 2008-02-29 , 39

Data in the Demonstration Tables

This section describes the data that is inserted into the base and derived tables by the previous code example. The values for InstanceRelationTypeId and RecId can vary between test runs, but they match among the tables in any particular run.

Rows in the Base Table

The following table shows the rows that are inserted into the TabPet base table. The value in the InstanceRelationTypeId field is the system ID of the derived table.

InstanceRelationTypeId	PetBirthDate	RecId
50011	1988-07-20	111222332
50011	2008-02-29	111222333

Rows in the Derived Table

The following table shows the rows that are inserted into the TabPetDog derived table.

NumberOfTeeth	RecId
42	111222332
39	111222333

Delete Cascade in Both Directions

This section describes the next code example, which continues where the previous example stops. This next example demonstrates the following cascading deletes:

• Base to derived – an X++ SQL delete_from statement that deletes all base table rows would cascade to delete all rows in all derived tables.

• Derived to base – an X++ SQL delete_from statement on the derived table can cascade to delete rows from the base table. If many tables derive directly from the same base table, the delete of all rows from one derived table would not affect base rows that are associated with other derived tables.

  Note

  This is different than the delete cascade behavior that occurs when you delete from a foreign key table. The delete of rows that contain a foreign key does not cascade to delete any rows from the primary key table.

    static void DeleteCascadeJob3D(Args _args)
    {
        TabPet tPet;
        TabPetDog tDog;
    
        // Delete_from statements, with exactly one row identified
        // in each Where clause.
    
        info("--- Delete from base, cascades to delete to derived.");
        delete_from tPet where tPet.PetBirthDate == 29\02\2008;
    
        info("--- Delete from derived, cascades delete to base.");
        delete_from tDog where tDog.NumberOfTeeth == 42;
    
        // Verify the tables are empty.
    
        info("--- List base table rows after the delete (should be none).");
        while select * from tPet
        {
            error("Error, the base table should be empty.");
        }
        info("--- List derived table rows after the delete (should be none).");
        while select * from tDog
        {
            error("Error, the derived table should be empty.");
        }
    }

The following output to the Infolog is generated by the previous program.

Message (09:55:54 am)

--- Delete from derived, cascades delete from to base.

--- Delete from base, cascades to delete from derived.

--- List base table rows after the delete (should be none).

--- List derived table rows after the delete (should be none).

See also

Table Inheritance Overview

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.