14.5 ALTER TRIGGER Statement
The ALTER
TRIGGER
statement enables, disables, compiles, or renames a database trigger.
Note:
This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, use the "CREATE TRIGGER Statement" with the OR
REPLACE
clause.
Topics
Prerequisites
If the trigger is in the SYS
schema, you must be connected as SYSDBA
. Otherwise, the trigger must be in your 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.
See Also:
"CREATE TRIGGER Statement" for more information about triggers based on DATABASE
triggers
Syntax
alter_trigger ::=
trigger_compile_clause ::=
Semantics
alter_trigger
schema
Name of the schema containing the trigger. Default: your schema.
trigger_name
Name of the trigger to be altered.
[ ENABLE | DISABLE ]
Enables or disables the trigger.
RENAME TO new_name
Renames the trigger without changing its state.
When you rename a trigger, the database rebuilds the remembered source of the trigger in the *_SOURCE
static 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.
{ EDITIONABLE | NONEDITIONABLE }
Specifies whether the trigger becomes an editioned or noneditioned object if editioning is later enabled for the schema object type TRIGGER
in schema
. Default: EDITIONABLE
. For information about altering editioned and noneditioned objects, see Oracle Database Development Guide.
Restriction on NONEDITIONABLE
You cannot specify NONEDITIONABLE
for a crossedition trigger.
trigger_compile_clause
Recompiles the trigger, whether it is valid or invalid.
See compile_clause and compiler_parameters_clause semantics.Examples
Example 14-5 Disabling Triggers
The sample schema hr
has a trigger named update_job_history
created on the employees
table. The trigger fires 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.
Example 14-6 Enabling Triggers
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
In this chapter:
In other chapters: