Change Status

Go Up to Available object actions by DBMS

You can change the status of a number of relevant object types. For details, see the following topics:

• Change Status (Oracle, SQL Server, and Sybase ASE triggers)
• Change Status (SQL Server DDL triggers)
• Change Status (Full-text Indexes)
• Change Status (check constraints)
• Change Status (tablespaces)

Change Status (Oracle, SQL Server, and Sybase ASE triggers)

The Change Status object action lets you build an issue an ALTER TABLE statement that specifies a DISABLE (DISABLE TRIGGER) or ENABLE (ENABLE TRIGGER) option. This lets you enable or disable any triggers.

Loading a database from a previous dump causes any triggers defined in the database to fire. To speed the time required to load a database you should disable triggers.

Note: Disabling triggers can lead to problems with maintaining referential integrity and business rules

To enable or disable a trigger:

1. Initiate a Change Status action against one or more triggers. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in the Change Status wizard:

Step	Settings and tasks
Action Options	Select the Enabled check box to enable the trigger. Deselect it to disable the trigger.
Dependencies	Lists referring and referenced objects potentially impacted by the change. For details, see Dependencies.
Preview	Displays the DDL that will execute the object action. For details, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.

Change Status (SQL Server DDL triggers)

In Rapid SQL, the Change Status object action lets you build and issue a DISABLE TRIGGER... ON DATABASE or ENABLE TRIGGER... ON DATABASE statement. This lets you enable or disable one or more database triggers.

Note: The Rapid SQL Browser listing for a database trigger indicates its current status. For more information, see Browsers.

To enable or disable a trigger:

1. Initiate a Change Status action against one or more triggers. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in the Change Status wizard:

Step	Settings and tasks
Action Options	Select the Enabled check box to enable the trigger. Deselect it to disable the trigger.
Dependencies	Lists referring and referenced objects potentially impacted by the change. For details, see Dependencies.
Preview	Displays the DDL that will execute the object action. For details, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.

Change Status (Full-text Indexes)

The action builds and submits an ALTER FULLTEXT INDEX statement with an ENABLE or DISABLE argument, letting you enable or disable the full-text index. Full-text searching is unavailable on tables or views with a disabled full-text index.

Platform Availability

• SQL SVR

To Enable or Disable a Full-text Index

1. Initiate a Change Status action against one or more full-text indexes. For more information, see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in the dialog:

Step	Settings and tasks
Action Options	Select the Enabled check box to enable the full-text index. Deselect the Enabled check box to disable the full-text index. Note that on opening the dialog, the Enabled check box state reflects whether the index is currently enabled or disabled.
Preview	Preview the DDL generated for the operation. For more information, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.

Change Status (check constraints)

You can change the enabled/disabled status of check constraints, foreign key constraints, primary key constraints, and unique key constraints.

For detailed instructions, see:

• Setting constraint status for Microsoft SQL Server objects
• Setting Constraint Status for Oracle Objects

Setting constraint status for Microsoft SQL Server objects

The Set Constraint Status dialog lets you specify the ability of a group of constraints to be replicated, and (for Microsoft SQL Server version 7 or later) enable or disable check constraints, foreign key constraints, primary key constraints, and unique key constraints.

Completing the Set Constraint(s) Status Dialog Box

To complete this dialog, do the following:

1. Initiate a Change Status action against one or more check constraints. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying the settings on the Change Status wizard:

Step	Settings and tasks
Action Options	Enabled	Deselect to temporarily override listed check constraints. Useful when you need to execute special processes that would ordinarily incur constraint-related errors.
	Not for Replication	When you duplicate the table schema and data of a source database containing constraints marked "Not for Replication", these objects are not carried over to the duplicate of the schema.
Dependencies	View the dependencies on the tablespace. For more information, see Dependencies.
Preview	Preview the DDL generated from your choices. For more information, see Preview.

3. Use the Execute or Schedule button to complete the operation.

Setting Constraint Status for Oracle Objects

The Set Constraint(s) Status dialog lets you change the status of check constraints, foreign key constraints, primary key constraints, and unique key constraints. You can enable or disable selected constraints and, in the case of primary key and unique key constraints, lets you enable with or without validation and disable with or without the changes cascading.

When enabled, the rule defined by the constraint is enforced on the data values in the columns on which the constraint is placed. When disabled, the constraint rule is not enforced but the constraint continues to be stored in the data dictionary.

Temporarily disabling constraints can improve performance when you are loading large amounts of data or when you are making massive changes to a table. Disabling constraints also can be useful if you are importing or exporting one table at a time.

Note: Primary keys for index-organized tables cannot be disabled.

Note: You cannot drop a unique or primary key constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, select the Cascade check box in the Set Constraint(s) Status dialog

The table below describes the options and functionality on the Set Constraint(s) Status dialog.

Note: The options differ by object.

Option	Description
Enable	Enabling the constraint and not selecting the Validate check box automatically uses Oracle ENABLE NOVALIDATE clause which enables a constraint so that it does not validate the existing data. A table using constraints in enable novalidate mode can contain invalid data but you cannot add new invalid data to that table.The enable novalidate mode is useful as an intermediate state or when you do not want the constraint to check for possible exceptions (e.g., after a data warehouse load).
Validate	Enabling the constraint and selecting the Validate check box causes Oracle to validate all existing data in the key columns of the table with the constraint. If an exception exists, Oracle returns an error and the constraint remains disabled.
Cascade	Selecting the Cascade check box when disabling a primary key or foreign key constraint instructs Oracle to simultaneously disable any constraints that depend on the primary or unique key. Selecting the Delete Cascade check box instructs Oracle to delete data in the child table (on which the foreign key is defined) if the referenced data is the parent table is deleted.

Completing the Set Constraint(s) Status Dialog Box

To complete this dialog box, do the following:

1. Initiate a Change Status action against one or more check constraints. For more information see Initiating an object operation.
2. Use the table above to select dialog box options.
3. Use the Execute or Schedule button to complete the operation.

Change Status (tablespaces)

You can change the online, offline, or read only status of a tablespace to control access to its segments. In addition, when setting a tablespace offline, you can choose between NORMAL, TEMPORARY, or IMMEDIATE modes of taking the tablespace offline.

Platform Availability

• ORCL

To Change the Status of a Tablespace

1. Initiate a Change Status action against one or more tablespaces. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying settings in the Change Status wizard:

Step	Settings and tasks
Action options	From the Change Status dropdown, select ONLINE, OFFLINE, or READ ONLY. If you select OFFLINE, from the OfflineMode dropdown, select NORMAL, TEMPORARY, or IMMEDIATE.
Dependencies	Review the referring and referred objects that will be automatically resolved when you execute this operation. For more information, see Dependencies.
Preview	Preview the DDL generated for the operation. For more information, see Preview.