SAVEPOINT

SAVEPOINT defines a point in a transaction to which you can roll back.

Syntax

SAVEPOINT savepoint-name

savepoint-name ::= user-defined-name

Remarks

ROLLBACK, SAVEPOINT, and RELEASE are supported at the session level (outside of stored procedures) only if AUTOCOMMIT is off. Otherwise, ROLLBACK, SAVEPOINT, and RELEASE must be used within a stored procedure.

Any committed statements within a stored procedure are controlled by the outermost transaction of the calling SQL application.

A SAVEPOINT applies only to the procedure in which it is defined. That is, you cannot reference a SAVEPOINT defined in another procedure.

Examples

The following example sets a SAVEPOINT then checks a condition to determine whether to ROLLBACK or to RELEASE the SAVEPOINT.

CREATE PROCEDURE Enroll_student( in :student ubigint, IN :classnum integer);

BEGIN

DECLARE :CurrentEnrollment INTEGER;

DECLARE :MaxEnrollment INTEGER;

SAVEPOINT SP1;

INSERT INTO Enrolls VALUES (:student, :classnum, 0.0);

SELECT COUNT(*) INTO :CurrentEnrollment FROM Enrolls WHERE class_id = :classnum;

SELECT Max_size INTO :MaxEnrollment FROM Class WHERE ID = :classnum;

IF :CurrentEnrollment >= :MaxEnrollment THEN

ROLLBACK to SAVEPOINT SP1;

ELSE

RELEASE SAVEPOINT SP1;

END IF;

END;

Note that COUNT(expression) counts all nonnull values for an expression across a predicate. COUNT(*) counts all values, including null values.