Reference Manual
   CHAPTER 9. SQL Statements     

PREPARE TO COMMIT statement

Function 

To check whether a COMMIT can be performed.

Syntax 

PREPARE TO COMMIT

Permissions 

None.

Side effects 

None.

See also 

COMMIT statement

ROLLBACK statement

Description 

The PREPARE TO COMMIT statement tests whether a COMMIT can be performed successfully. The statement will cause an error if a COMMIT is not possible without violating the integrity of the database.

Standards and compatibility 

• SQL/92     Vendor extension.

• Sybase     Not supported in Adaptive Server Enterprise.

Examples 

• The following sequence of statements leads to an error because of foreign key checking on the employee table.

  EXECUTE IMMEDIATE 
     "SET OPTION wait_for_commit = 'on'";
  
  EXECUTE IMMEDIATE "DELETE FROM employee
     WHERE emp_id = 160";
  
  EXECUTE IMMEDIATE "PREPARE TO COMMIT";

• The following sequence of statements allows the delete to take place, even though it causes integrity violations. The PREPARE TO COMMIT statement returns an error.

  SET OPTION wait_for_commit= 'ON' ;
  
  DELETE
  FROM department
  WHERE dept_id = 100 ;
  
  PREPARE TO COMMIT ;