Cascaded insert transactions

To implement a cascaded insert operation, that will insert at the same time a continent, a country on that continent and a city within the newly inserted country you would naturally have to use three insert transactions. However, in MX Kollection 3 there is a wizard that performs a cascaded insert for two tables: the Insert Into Two Tables Wizard.

It is now easy to understand that in order to create a cascaded insert operation for three table, you could simply combine a simple Insert and an Insert Into Two Tables. Just placing two insert operations on the same page does not help in any way to pass the record ID of one transaction to the other.

To link two transactions, you have to use the Link Transaction server behavior.

To create the insert page, follow the next steps:

1. Open the index.php page in Dreamweaver. Apply the Insert Record From Wizard and configure it as done in the other tutorials, with the following data:
   ·  Connection: connInsert.
   ·  Table: continent_con.
   ·  After inserting, go to: results.php.
   ·  Fields: name_con, displayed as text field, with Label: "Continent:"

2. Once the wizard configuration is done, apply the Insert Into Two Tables Wizard, to create the insert form for the countries and cities tables. Configure the wizard by using the following data:
   ·  Connection: connInsert.
   ·  Master table: country_cnt.
   ·  Detail table: city_cit.
   ·  Foreign key column: idcnt_cit.
   ·  After inserting, go to: leave this field empty.
   Note: when using the Link transaction trigger to execute multiple transactions, the detail transaction must not have a redirect page set; the master's transaction redirect will execute.
   
   
    

3. In the second step of the wizard, you have to determine what fields will be displayed and submitted for the master table. When deciding on the fields, you should remember that this is a master table in the current insert transaction, but a slave table when compared to the continent_con table:
   ·  name_cnt - change its label to Country.
   ·  idcon_cnt - set this field to be displayed as a hidden field.
   
   
    

4. In the third step, the fields of the city_cit table have to be configured. Only change the label for name_cit to City.

5. The fourth and fifth step allow defining validation rules, and are available only if you have MX Form Validation installed. Since this is not the goal of this tutorial, you can safely skip these steps, and hit the Finish button.

Now your page performs two insert transactions, through the two forms on page: one that adds data into the continent table, and one that adds data into the countries and cities tables. When viewed in Dreamweaver, the page looks like:


 

What needs to be done next is link these two separate insert transactions in order for the ID of the continent to be passed to the country insert operation, and so on. The tool that links two separate transactions is the Link Transaction server behavior.

1. Apply the Link Transactions server behavior from Server Behaviors -> + -> MX Kollection -> Forms -> Advanced -> Link Transactions.

2. Configure the dialog box that opens, specifying the following data:
   ·  In the Master transaction drop-down menu select the master insert transaction: the one that ads a new continent: ins_continent_con.
   ·  In the Slave transaction drop-down menu select the (slave) country insert transaction: ins_country_cnt.
   ·  In the Foreign key field drop-down menu select the slave table's column that acts as a foreign key to the master table.
   
   
    

Now the correct ID is passed to the second insert operation, as imposed by the newly inserted Link transaction server behavior. There is still no way to execute the two transactions at the same time, as they are placed on different forms, with different submit buttons and conditions. Therefore, you will need to change the page manually, in the following manner:

1. First, delete the first insert transaction's submit button. Select it in design view and hit the Delete button.

2. Next, switch the page to code view and delete the closing form tag of the first form, as well as the opening tag of the second form. Now the transactions share the same form.
   
   
   
   Note: if the slave form has the enctype attribute set, you have to copy it to the master transaction's opening <form> tag. This happens when the third form contains a file field.
    

3. The last step to take is to set the correct starting condition for the first insert operation. To do so, double click the InsertTransaction(continent_con,ins_continent_con) server behavior from the list displayed in the server behaviors tab.

4. In the dialog box that opens, change the First Check Variable form element name from KT_Insert1 to KT_Insert2.
   
   
    

5. With this last modification, the transactions will all start at the same time, using the same submit button and form.

You can save the page and preview it in the browser. Enter data in all three fields and hit the Insert record button:

If you check with your database server management software, you will notice that all three records have been inserted properly, with the correct values for the foreign keys.

The continent_con table:

The country_cnt table:

The city_cit table:

Now it is time to create the results page. It will display all records in all three tables, in order to show that the insert operation has performed successfully. To do so, you will have to add a recordset for each of the tables, retrieving all data, and three dynamic tables, each for a recordset. After creation, the page will display all data, including the ones entered through the cascaded insert transaction.

Whenever you need to insert data into several tables (two, three or even more), this is the correct way to do it. Simply place a transaction for the single inserts, or use the Insert Into Two Tables Wizard for double inserts, and link them all together by using the Link Transaction server behavior and a little change in the page's code.