User Guide

Table Of Contents

460 Chapter 19: Introduction to Databases and SQL

Updating multiple tables

The examples in this section all 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:

INSERT INTO Employees (FirstName,LastName,EMail,Phone,Department)

VALUES ('Simon', 'Horwith', 'SHORWITH','(202)-797-6570','Research and

Development')

</cfquery>

SELECT MAX(Emp_ID) AS New_Employee

FROM Employees

</cfquery>

</cftransaction>

Writing queries using an editor

Dreamweaver MX 2004 and HomeSite+ provide a graphical user interface (GUI) for writing and

executing queries. A GUI is useful for developing and testing your queries before you insert them

into a ColdFusion application.

This section contains a brief description of these GUIs. For more information, see the

documentation on your specific tool.

Writing queries using Dreamweaver MX 2004

This section describes how to define a query using the Dreamweaver MX 2004 Recordset dialog

box, which allows you to create a record set without having to manually enter SQL statements.

Defining a record set using this method can be as easy as selecting a database connection and table

from the pop-up menus.

To define a record set without writing SQL:

In the Dreamweaver Document window, open the page that will use the record set.

To open the Data Bindings panel, select Window > Data Bindings.