Database Reference
In-Depth Information
Note:
Once again, be aware of conflict with concurrent applications and
potentially upsetting clients.
15.2.3
The SAVEPOINT Command
Another transaction-related command you may want to use is the SAVE-
POINT command. The syntax is simply as follows, where the label implies
a point within a transaction to undo changes back to:
SAVEPOINT label;
SAVEPOINT is useful when you are making many changes to the data-
base and you want the ability to undo only part of the changes made. For
example, you have inserted some testing rows into a table specifically to test
an UPDATE command. You want to be able to undo the UPDATE com-
mand while keeping the inserted rows. This way, you can repeat the
UPDATE command.
Demonstrating using the SAVEPOINT command, we can do the fol-
lowing: Begin by updating a zip code and creating a target label (SAVE-
POINT). Then make a different change to the same row already updated
and query to see row changes. The result of the following script is shown in
Figure 15.5.
UPDATE ARTIST SET ZIP='10099'
WHERE NAME = 'Puddle of Mudd';
SAVEPOINT AFTERUPDATE;
UPDATE ARTIST SET NAME='Mud Puddle'
WHERE NAME = 'Puddle of Mudd';
SELECT ARTIST_ID, NAME, ZIP FROM ARTIST
WHERE NAME = 'Mud Puddle';
In the next script, we undo (rollback) the name change, done after the
SAVEPOINT label, and query again. We see that the name change no
longer exists, but the zip code is still changed. In other words, the first
update is stored and the second is removed. The result of the following
script is shown in Figure 15.6.
