SET AUTODDL
Go Up to isql Command Reference
Specifies whether DDL statements are committed automatically after being executed or committed only after an explicit COMMIT.
SET AUTODDL [ON | OFF];
| Argument | Description |
|---|---|
|
|
Turns on automatic commitment of DDL [default] |
|
|
Turns off automatic commitment of DDL |
Description: SET AUTODDL is used to turn on or off the automatic commitment of data definition language (DDL) statements. By default, DDL statements are automatically committed immediately after they are executed, in a separate transaction. This is the recommended behavior.
If the OFF keyword is specified, auto-commit of DDL is then turned off. In OFF mode, DDL statements can only be committed explicitly through a user’s transaction. This mode is useful for database prototyping, because uncommitted changes are easily undone by rolling them back.
SET AUTODDL has a shorthand equivalent, SET AUTO.
The
ON and OFF keywords are optional. If they are omitted, SET AUTO switches from one mode to the other. Although you can save typing by omitting the optional keyword, including the keyword is recommended because it avoids potential confusion.Examples: The following example shows part of an isql script that turns off AUTODDL, creates a table named TEMP, then rolls back the work.
. . .
SET AUTO OFF;
CREATE TABLE TEMP (a INT, b INT);
ROLLBACK;
. . .
This script creates TEMP and then rolls back the statement. No table is created. because its creation was rolled back.
The next script uses the default AUTODDL ON. It creates the table TEMP and then performs a rollback:
. . .
CREATE TABLE TEMP (a INT, b INT);
ROLLBACK;
. . .
Because DDL is automatically committed, the rollback does not affect the creation of TEMP.