BEGIN/END TRANSACTION Examples | SQL Statements | VantageCloud Lake - Examples: BEGIN/END TRANSACTION - Teradata VantageCloud Lake

The following scenarios show the use of BEGIN TRANSACTION/END TRANSACTION.

Example: Explicit Transaction to Delete

Assuming the department table has an emp_count column, the following explicit transaction removes a row from the employee table and then decrement a departmental head count in the department table.

     BEGIN TRANSACTION;
      DELETE FROM employee
      WHERE name = 'Reed C';
      UPDATE department
      SET emp_count = emp_count -1
      WHERE dept_no = 500;
     END TRANSACTION;

Example: Explicit Transaction to Insert

The following example shows an explicit transaction in which each INSERT statement is associated with a USING request modifier.

      BEGIN TRANSACTION ;
      USING ssnumfile (INTEGER)
      INSERT INTO employee (soc_sec_no) VALUES (:ssnumfile) ;
         USING ssnumfile (INTEGER)
      INSERT INTO employee (soc_sec_no) VALUES (:ssnumfile) ;
         USING ssnumfile (INTEGER)
      INSERT INTO employee (soc_sec_no) VALUES (:ssnumfile) ;
     END TRANSACTION ;

Example: Explicit Transaction for a DDL Statement

The following examples show the use a DDL statement in an explicit transaction. These transactions create a volatile table, perform an aggregate operation on the result of another aggregate operation, and then drop the volatile table that was created in the first transaction.

Two transactions are used because a DDL statement must be either the only statement in a transaction or the last statement in a transaction

     BEGIN TRANSACTION;
      CREATE VOLATILE TABLE dept_sum_sal NO LOG (
        dept_no SMALLINT FORMAT '999' BETWEEN 100 AND 900 NOT NULL,
        sum_sal DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99')
      PRIMARY INDEX(dept_no),
      ON COMMIT DELETE ROWS;
     END TRANSACTION;

     BEGIN TRANSACTION;
      INSERT INTO dept_sum_sal
      SELECT dept_no, SUM(salary)
      FROM employee
      GROUP BY dept_no;
      SELECT AVG(sum_sal)
      FROM dept_sum_sal;
      DROP VOLATILE TABLE dept_sum_sal;
     END TRANSACTION;

Example: Implicit Transaction (BTEQ)

The following statement is structured as a BTEQ multiple-statement request, processed as a single implicit transaction.

The placement of USING and the semicolons makes sure the failure of any WHERE conditions causes the transaction to end and all completed insert and update operations to be rolled back.

     USING var1(CHARACTER),
           var2(CHARACTER),
           var3(CHARACTER)
      INSERT INTO test_tab_u (c1) VALUES (:var1)
      ; INSERT INTO test_tab_u (c1) VALUES (:var2)
      ; INSERT INTO test_tab_u (c1) VALUES (:var3)
      ; UPDATE test_tab_u SET c2 = c1 + 1
         WHERE c1 = :var1
      ; UPDATE test_tab_u SET c2 = c1 + 1
         WHERE c1 = :var2