Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Transactions

Like most databases, Oracle implements the concept of a transaction, which is a set of related statements that either all execute or do not execute at all. Transactions play an important role in maintaining data integrity.

Protecting Data Integrity

Example 4-17 shows one method for changing a project number from 1001 to 1006:

1. Because rows in the project_hours table must always point to valid project rows, the example begins by creating a copy of project 1001 but gives that copy the new number of 1006.

2. With project 1006 in place, it's then possible to switch the rows in project_hours to point to 1006 instead of 1001.

3. Finally, when no more rows remain that refer to project 1001, the row for that project can be deleted.

Example 4-17. Changing a project's ID number

--Create the new project
INSERT INTO project
   SELECT 1006, project_name, project_budget FROM project
   WHERE project_id = 1001;

--Point the time log rows in project_hours to the new project number
UPDATE project_hours
SET project_id = 1006
WHERE project_id = 1001;

--Delete the original project record
DELETE FROM project
WHERE project_id=1001;

You'll encounter two issues when executing a set of statements such as those shown in Example 4-16. First, it's important that all statements be executed. Imagine the mess if your connection dropped after only the first INSERT statement was executed. Until you were able to reconnect and fix the problem, your database would show two projects, 1001 and 1006, where there should only be ...