Scenarios - Teradata Database

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

Scenario: Explicit Transaction to Delete

Assuming that the department table contains an emp_count column, the following explicit transaction could be used to remove 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;

Scenario: Explicit Transaction to Insert

The following example illustrates 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 ;

Scenario: Explicit Transaction for a DDL Statement

The following examples illustrate 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;

Scenario: Implicit Transaction (BTEQ)

The following example is structured as a BTEQ multistatement request, so it is processed as a single implicit transaction.

Note the placement of the USING modifier and the semicolons. With this construct, the failure of any WHERE conditions causes the transaction to abort 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