Linking transactions

A transaction is a group of SQL statements whose effects are logically connected. Anything from simple queries to inserting, and deleting operations can be considered a transaction, as well as more complex groups of several statements which accomplish a specific task.

A foreign key is a field from a database table that refers to (or targets) a specific key, usually the primary key, in another table. This is a convenient way of logically linking information from related tables in the same database. For instance, a table that stores information about products can contain a foreign key that references the primary key field in a table that stores manufacturers. This way, each product has an associated manufacturer – its associated foreign key points to the unique identifier of the manufacturer. Please note that the foreign key is not unique, but the referenced field (the primary key in the referenced table) usually is.database table that refers to (or targets) a specific key, usually the primary key, in another table. This is a convenient way of logically linking information from related tables in the same database. For instance, a table that stores information about products can contain a foreign key that references the primary key field in a table that stores manufacturers. This way, each product has an associated manufacturer – its associated foreign key points to the unique identifier of the manufacturer. Please note that the foreign key is not unique, but the referenced field (the primary key in the referenced table) usually is.

A database refers to data organized and stored on a computer that can be searched and retrieved by a computer program. Most industrial-strength and many smaller database applications can be addressed using SQL (Structured Query Language).

A query is a SQL command that will extract information from the tables of a database. Essentially, a query is a request for information from your database.

A trigger is a SQL procedure that performs an action when a transaction (INSERT, UPDATE, DELETE) occurs. You can use triggers to perform validation of input data, to automatically generate a value for a newly inserted row, to read from other tables for cross-referencing purposes, or to support alerts through e-mail messages.

A primary key is one or more table columns whose values uniquely identify each record in a table. In general, a primary key is defined on a single column, but it is not uncommon to have it defined on two columns.

Linking transactions

One more improvement in the Transaction Engine 3 is the ability to link two transactions, and this way, to insert data into two tables at the same time.

In order to insert/update into two tables at once, the two separate transactions must be added first. This can either be with the wizard, or with the Server behaviors and Dreamweaver tools. One mention has to be made concerning the second (also known as detail) transaction: all fields must be displayed on the form, including primary and foreign key.

After creating the two transactions, you must apply the Link Transactions trigger, which will pass the first's transaction primary key as the second's foreign key and will submit them both.

The code behind this trigger is similar to the one shown below:

//start trigger LinkTransactions
function KT_TriggerAFTER_LinkTransactions(&$tNG) {
global $ins_contact_con;
$LinkTransactions = new tNG_linkedTrans($tNG, $ins_contact_con);
$LinkTransactions->setLink("idcom_con");
return $LinkTransactions->execute();
}
//end trigger LinkTransactions

As you can see, it is an after trigger, that executes only after the first (master) transaction took place and inserted data into its table. It then passes the primary key to the foreign key , which is used to determine the link in the setLink method. It is also an error trigger, as if anything happens, it will roll back the executed transaction(s).

Just as for any other Transaction Engine 3 trigger or transaction, all code works with objects and methods, and this shows in the clear code.