RELEASE

Example

START TRANSACTION;
INSERT INTO students (first_name, last_name)
VALUES ('John', 'Doe');
RELEASE;

Output

Query OK, 1 row affected (0.01 sec)

Explanation

A START TRANSACTION statement is used which opens a new transaction. An INSERT statement is used next inside the transaction, which adds a new row to the students table. The RELEASE statement commits the transaction, which causes the changes to become permanent.

Example

BEGIN TRANSACTION;
    -- Perform operations here…
ROLLBACK;

Output

No visible output will be provided.

Explanation

The example demonstrates a sample transaction in SQL server. If anything goes wrong during transaction, ROLLBACK command will take the database back to the state where it was at the time BEGIN TRANSACTION was executed. ROLLBACK essentially releases the transaction, hence it is a way to RELEASE in SQL server. There is no visible output for this command but the actions performed during transaction are undone.

Example

DECLARE
    v_test NUMBER(2);
BEGIN
    SAVEPOINT start_trans;

    UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;

    INTO v_test SELECT COUNT(*) FROM accounts WHERE balance < 0;

    IF v_test > 0 THEN
        ROLLBACK TO start_trans;
        DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
    ELSE
        RELEASE SAVEPOINT start_trans;
        DBMS_OUTPUT.PUT_LINE('Savepoint released.');
    END IF;
END;
/

Output

Savepoint released.

Explanation

The provided PL/SQL block creates a savepoint called start_trans. The next statement is an UPDATE statement which subtracts 500 from the balance of the account with id 101. After that, a SELECT statement counts the number of accounts with a balance less than 0 and saves the result in v_test. If v_test is bigger than 0, the block rolls back the transaction to the start_trans savepoint. If v_test is 0, the block releases the start_trans savepoint. In both cases, the block outputs a corresponding information line.