A transaction needs to have a starting and ending point. The starting point can be implied, for example, by starting a host program, or it can be explicit with a START TRANSACTION or a BEGIN TRANSACTION. The ending point is explicit with either a COMMIT or ROLLBACK command.

SQL Server starts with BEGIN TRANSACTION.

     BEGIN TRANSACTION;
       INSERT INTO MANAGER_TBL
         ('STOR_NO', 'MGR_NO', 'MGR_NAME')
           SELECT STOR_ID, EMPL_ID, EMPL_NAME
             FROM EMPLOYEE_TBL
            WHERE TITLE = 'STORE MANAGER';
     COMMIT;

SQL:1999, MySQL, PostgreSQL, and SQL Server switched to the new SQL standard START TRANSACTION:

     START TRANSACTION;
       INSERT INTO MANAGER_TBL
         ('STOR_NO', 'MGR_NO', 'MGR_NAME')
           SELECT STOR_ID, EMPL_ID, EMPL_NAME
             FROM EMPLOYEE_TBL
            WHERE TITLE = 'STORE MANAGER';
     COMMIT;

> Access transaction control starts with BeginTrans and ends with CommitTrans or RollbackTrans.

> DB2 and ORACLE always have an implied starting point, so they do not need an explicit starting statement.

A transaction is a sequence of one or more SQL statements that are to be executed together as a group. If any part of the transaction’s statements fails, then all actions taken by the statements within the transaction must be reversed. It is an all-or-nothing requirement.If all statements in a single transaction are successful, then all actions taken are made permanent with a COMMIT command. On the other hand, if any of the statements within a transaction fail, then all actions must be reversed with a ROLLBACK command.

An example of a transaction in accounting would be a set of debit and credit entries where the amount of the debit entry exactly matches the amount of the credit entry.

It is your responsability to start and end a transaction at a point that creates a logical transaction. It should be as small as possible while maintaining a logical unit.

See the following EXAMPLEs.

If you wish to UPDATE or DELETE a row in a host program, the DECLARE CURSOR must include FOR UPDATE. If columns are not stated, all columns can be updated. Syntax is:

     EXEC SQL
       DECLARE cursor_name [CURSOR FOR]
        SELECT column1, column2 ...
          FROM table_name ...
         WHERE ...
          [FOR UPDATE [OF column1, column2 ...]]
     END-EXEC

This SELECT FOR UPDATE can select only one table or view. DISTINCT, ORDER BY, and UNION can not be used. The DECLARE CURSOR FOR UPDATE would look like this:

     EXEC SQL
       DECLARE VENDOR_CSR
        SELECT VENDOR_NAME, STATUS
          FROM VENDOR_TBL
         WHERE COUNTRY = :SEARCH-COUNTRY
           FOR UPDATE OF STATUS
     END-EXEC

A column that is to be updated does not have to appear in the SELECT statement. The OPEN, FETCH, and CLOSE statements used for UPDATE or DELETE are identical to those used for retrieval. However, UPDATE or DELETE must have the phrase WHERE CURRENT OF. If WHERE CURRENT OF is not stated, the entire table will be updated or deleted.

     EXEC SQL
       UPDATE table_name
          SET column1 = value1,
              column2 = value2 ...
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       DELETE table_name
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       UPDATE VENDOR_TBL
          SET STATUS = :WS-STATUS
        WHERE CURRENT OF VENDOR_CSR
     END-EXEC