TRANSACTION in Teradata

Option 1 : BTEQ utility without explicit transaction

When we use BTEQ, It involves taking advantage of the way BTEQ delivers the SQL to the optimizer. The trick is to continue each subsequent SQL statement on the same line as the semi-colon (;) of the previous statement.
When BTEQ finds this condition, it automatically delivers the commands as a single transaction.
The following example demonstrates this technique:

UPDATE tbl_employee
SET emp_id=101
WHERE emp_id=100
; UPDATE tbl_employee
SET emp_id=102
WHERE emp_id=106


In the above script, both updates must work successfully, or both will be rolled back because the second UPDATE is on the same line as the semi-colon for the first UPDATE. When a semicolon (;) is not the last thing on a line, BTEQ treats the next SQL as part of the same transaction.

Option 2 : BEGIN and END Transactions

BEGIN TRANSACTION (BT) and END TRANSACTION (ET) commands can be used for the transaction in Teradata mode.
They should primarily be used in BTEQ.
Syntax:
BT;
statements;
ET;


Example:
BT;
DELETE FROM tbl_table;
INSERT INTO tbl_table VALUES(1);
ET;

The above code should be keenly looked.
Take a case, if the first DELETE statement fails, what will happen ? Will the transaction be terminated ? ... NO
Here, BTEQ plays with implicit transaction.
Although, the first statement fails, BTEQ executes the second statement since no act of rectifying errors specified.

So, the solution is to use the error codes after each statements.

Example:
BT;
DELETE FROM tbl_table;
.if errorcode>0 then .quit 12
INSERT INTO tbl_table VALUES(1);
ET;

OR
BT;
DELETE FROM tbl_table;
.if errorcode>0 then .goto endtrans
INSERT INTO tbl_table VALUES(1);
ET;

.label endtrans
.quit