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 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.

See Also

COMMIT

CREATE PROCEDURE

RELEASE SAVEPOINT

ROLLBACK