|
|
|
Valid in: SQL, ESQL
The Savepoint statement declares a named savepoint marker within a transaction. Savepoints can be used in conjunction with the rollback statement to rollback a transaction to the specified savepoint when necessary. Using savepoints can eliminate the need to roll back an entire transaction if it is not necessary.
Note: This statement has additional considerations when used in a distributed environment. For more information, see the Ingres Star User Guide.
|
|
|
The Savepoint statement has the following format:
[EXEC SQL] SAVEPOINT savepoint_name;
Can be any unquoted character string conforming to rules for object names, except that the first character need not be alphabetic. This enables numeric savepoint names to be specified.
Any number of savepoints can be declared within a transaction, and the same savepoint_name can be used more than once. However, if the transaction is aborted to a savepoint whose name is used more than once, the transaction is backed out to the most recent use of the savepoint_name.
All savepoints of a transaction are rendered inactive when the transaction is terminated (with either a commit, a rollback, or a system intervention upon deadlock). For more information on deadlock, see Commit and Rollback and the chapter "Working with Transactions and Handling Errors."
|
|
|
An embedded Savepoint statement cannot be issued when a cursor is open. Savepoint_name cannot be specified with a host language variable.
|
|
|
All users are permitted to use this statement.
|
|
|
|
|
|
The following example declares savepoints among other SQL statements:
exec sql insert into emp (name, sal, bdate)
values ('Jones,Bill', 10000, 1945);
/*set first savepoint marker */
exec sql savepoint setone;
exec sql insert into emp (name, sal, bdate)
values ('Smith,Stan', 20000, 1948);
/* set second savepoint marker */
exec sql savepoint 2;
exec sql insert into emp (name, sal, bdate)
values ('Engel,Harry', 18000, 1954);
/* undo third append;first and second remain */
exec sql rollback to 2;
/* undoes second append; first remains */
exec sql rollback to setone;
exec sql commit;
/* only the first append is committed */