BEGIN [ATOMIC]

Remarks

It is often convenient to group individual statements so that they can be treated as a single unit. The BEGIN and END statements are used in compound statements to group the statements into a unit. You can use a compound statement only in the body of a stored procedure, a user-defined function, or in a trigger declaration.

ATOMIC specifies that the set of statements within the unit either all succeed or all fail. If one condition within the BEGIN ATOMIC . . . END unit is not met, no records are affected. If the condition should affect more than one row, all rows (or none) are affected. For any record to be affected, all the conditions within a BEGIN ATOMIC . . . END unit must return true.

Examples

In the following example, two UPDATEs are grouped as an ATOMIC unit. The Perm_State column in the Person table is updated only if all of the other conditions are true. That is, a record for Bill Andrew exists with 'TX' as the Perm_State, and a record for Yvette Lopez exists with 'OR' as the Perm_State. If any of these conditions are not true, neither record is updated. Assume the BEGIN . . . END unit is within a procedure.

BEGIN ATOMIC

UPDATE Person set Perm_State = 'MA' where Perm_State = 'TX' AND First_Name = 'Bill' AND Last_Name = 'Andrew';

UPDATE Person SET Perm_State = 'WA' WHERE Perm_State = 'OR' AND First_Name = 'Yvette' AND Last_Name = 'Lopez';

END