SAVEPOINT

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

SAVEPOINT savepoint-name

savepoint-name ::= user-defined-name

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 ODBC application. This means that, depending on the AUTOCOMMIT mode specified on SQLSetConnectOption, calling the stored procedure externally from an ODBC application performs one of two actions. It either commits automatically (AUTOCOMMIT on, the default) or waits for you to call SQLTransact with SQL_COMMIT or SQL_ROLLBACK (when AUTOCOMMIT is set to off).

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 non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.