 |
|
Oracle Tips by Burleson |
SAVEPOINT
A SAVEPOINT
is a marker within a transaction that
allows for a partial rollback. As changes are made in a transaction,
we can create SAVEPOINTs to mark different points within the
transaction. If we encounter an error, we can rollback to a SAVEPOINT
or all the way back to the beginning of the transaction.
SQL> INSERT
INTO AUTHOR
2 VALUES ('A11l', 'john',
3 'garmany', '123-345-4567',
4 '1234 here st', 'denver',
5 'CO','90204', '9999');
1 row
created.
SQL>
savepoint in_author;
Savepoint
created.
SQL> INSERT
INTO BOOK_AUTHOR VALUES ('A111', 'B130', .20);
1 row
created.
SQL>
savepoint in_book_author;
Savepoint
created.
SQL> INSERT
INTO BOOK
2 VALUES ('B130', 'P002', 'easy oracle sql',
3 'miscellaneous', 9.95, 1000, 15, 0, '',
4 to_date ('02-20-2005','MM-DD-YYYY'));
1 row
created.
SQL> rollback
to in_author;
Rollback
complete.
In the example above, I inserted a row into the
AUTHOR table and created a SAVEPOINT called
in_author. Next, I inserted
a row into the book_author
table and created another SAVEPOINT called
in_book_author. Finally, I
inserted a row in the BOOK table. I then issued a ROLLBACK to
in_author. At this point,
the row inserted into the AUTHOR table is still there and not
committed. The rows added to the book and
book_author tables have been
discarded. At this point, I can continue to make changes, issue a
ROLLBACK to the start of the transaction, or
issue a COMMIT and commit the row in the author table.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
