The preceding examples describe how to modify a single database table. However, you might have a database that uses multiple tables to represent information.

One way to update multiple tables is to use one INSERT statement per table and to wrap all INSERT statements within a database transaction. A transaction contains one or more SQL statements that can be rolled back or committed as a unit. If any single statement in the transaction fails, you can roll back the entire transaction, cancelling any previous writes that occurred within the transaction. You can use the same technique for selects, updates, and deletes. The following example uses the cftransaction tag to wrap multiple SQL statements:

<cftransaction>

<cfquery name="qInsEmp" datasource="cfdocexamples">
    INSERT INTO Employees (FirstName,LastName,EMail,Phone,Department)
    VALUES ('Simon', 'Horwith', 'SHORWITH','(202)-797-6570','Research and Development')
</cfquery>

<cfquery name="qGetID" datasource="cfdocexamples">
    SELECT MAX(Emp_ID) AS New_Employee
    FROM Employees
</cfquery>

</cftransaction>