ALTER TRIGGER Statement

The ALTER TRIGGER statement enables, disables, or compiles a database trigger.

Topics:

• Prerequisites

• Syntax

• Semantics

• Examples

• Related Topics

Prerequisites

If the trigger is in the SYS schema, you must be connected as SYSDBA. Otherwise, the trigger must be in your own schema or you must have ALTER ANY TRIGGER system privilege.

In addition, to alter a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.

Syntax

alter_trigger::=

compiler_parameters_clause::=

Semantics

schema

The name of the schema containing the trigger. The default is your own schema.

trigger

The name of the trigger to be altered.

ENABLE

Enables the trigger. You can also use the ENABLE ALL TRIGGERS clause of ALTER TABLE to enable all triggers associated with a table. See the ALTER TABLE statement in Oracle Database SQL Language Reference.

DISABLE

Disables the trigger. You can also use the DISABLE ALL TRIGGERS clause of ALTER TABLE to disable all triggers associated with a table.

RENAME TO new_name

Renames the trigger. The database renames the trigger and leaves it in the same state it was in before being renamed.

When you rename a trigger, the database rebuilds the remembered source of the trigger in the USER_SOURCE, ALL_SOURCE, and DBA_SOURCE data dictionary views. As a result, comments and formatting may change in the TEXT column of those views even though the trigger source did not change.

COMPILE

Recompiles the trigger, whether it is valid or invalid.

First, if any of the objects upon which the trigger depends are invalid, the database recompiles them.

If the database recompiles the trigger successfully, then the trigger becomes valid. Otherwise, the database returns an error and the trigger remains invalid.

During recompilation, the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them after compilation. To avoid this process, specify the REUSE SETTINGS clause.

DEBUG

Has the same behavior for a trigger as it does for a function. See DEBUG.

compiler_parameters_clause

Has the same behavior for a trigger as it does for a function. See the ALTER FUNCTION compiler_parameters_clause.

REUSE SETTINGS

Has the same behavior for a trigger as it does for a function. See REUSE SETTINGS.

Examples

Disabling Triggers: Example The sample schema hr has a trigger named update_job_history created on the employees table. The trigger is fired whenever an UPDATE statement changes an employee's job_id. The trigger inserts into the job_history table a row that contains the employee's ID, begin and end date of the last job, and the job ID and department.

When this trigger is created, the database enables it automatically. You can subsequently disable the trigger with this statement:

ALTER TRIGGER update_job_history DISABLE;
 

When the trigger is disabled, the database does not fire the trigger when an UPDATE statement changes an employee's job.

Enabling Triggers: Example After disabling the trigger, you can subsequently enable it with this statement:

ALTER TRIGGER update_job_history ENABLE; 

After you reenable the trigger, the database fires the trigger whenever an UPDATE statement changes an employee's job. If an employee's job is updated while the trigger is disabled, then the database does not automatically fire the trigger for this employee until another transaction changes the job_id again.

Related Topics

• "CREATE TRIGGER Statement"

• "DROP TRIGGER Statement"