Deleting Data - macromedia COLDFUSION MX 7 Web Application Construction Kit [Electronic resources] نسخه متنی

Deleting Data

Deleting data from a table is even easier than adding or updating dataperhaps too easy.

You use the SQL DELETE statement to delete data. The statement takes only two parametersone required and one optional:

• The name of the table from which to delete the data must be specified immediately following the words DELETE FROM.

• An optional WHERE clause can be used to restrict the scope of the deletion process.

The DELETE statement is dangerously easy to use. Look at the following line of code (but don't execute it):

DELETE FROM Directors

This statement removes all directors from the Directors table, and does it without any warnings or confirmation.

TIP

Some databases, in particular client/server databases (such as Microsoft SQL Server and Oracle), offer safeguards against accidental or malicious deletions. There generally are two approaches to preventing mass deletion.

One is to create a trigger (a piece of code that runs on the server when specific operations occur) that verifies every DELETE statement and blocks any DELETE without a WHERE clause.

A second is to restrict the use of DELETE without a WHERE clause based on login name. Only certain users, usually those with administrative rights, are granted permission to execute DELETE without a WHERE clause. Any other user attempting a mass DELETE will receive an error message, and the operation will abort.

Not all database systems support these techniques. Consult the database administrator's manuals to ascertain which safeguards are available to you.

The DELETE statement is most often used with a WHERE clause. For example, the following SQL statement deletes a single director (the one you just added) from the Directors table:

DELETE FROM Directors
WHERE DirectorID=14

To verify that the row was deleted, retrieve all the Directors one last time (as seen in Figure 7.6).