COMMIT

The COMMIT statement signals the end of a logical transaction and converts temporary data into permanent data.

Syntax

COMMIT [ ] 

Remarks

COMMIT (and START TRANSACTION) is supported only within stored procedures. You cannot use COMMIT or START TRANSACTION within the SQL Editor. (SQL Editor sets AUTOCOMMIT to "on.")

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

You may call multiple START TRANSACTION statements to start the nested transactions, but the outermost COMMIT controls whether any nested committed blocks are committed or rolled back. For example, if transactions are nested five levels, then five COMMIT statements are needed to commit all of the transactions. COMMIT does not release any lock until the outermost transaction is committed.

COMMIT and COMMIT WORK perform the same functionality.

Examples

The following example, within a stored procedure, begins a transaction which updates the Amount_Owed column in the Billing table. This work is committed; another transaction updates the Amount_Paid column and sets it to zero. The final COMMIT WORK statement ends the second transaction.

START TRANSACTION; 
UPDATE Billing B 
SET Amount_Owed = Amount_Owed - Amount_Paid 
WHERE Student_ID IN 
(SELECT DISTINCT E.Student_ID 
FROM Enrolls E, Billing B 
WHERE E.Student_ID = B.Student_ID); 

COMMIT WORK; 
START TRANSACTION; 
UPDATE Billing B 
SET Amount_Paid = 0 
WHERE Student_ID IN 
(SELECT DISTINCT E.Student_ID 
FROM Enrolls E, Billing B 
WHERE E.Student_ID = B.Student_ID); 

COMMIT WORK; 

CREATE PROCEDURE UpdateBilling( ) AS 
BEGIN 
START TRANSACTION; 
UPDATE Billing SET Amount_Owed =                                                
Amount_Owed + Amount_Owed; 
UPDATE Billing set Amount_Owed = Amount_Owed + 100 
WHERE Student_ID = 10; 
COMMIT; 

END; 

See Also

CREATE PROCEDURE

ROLLBACK

START TRANSACTION