Use the ALTER
TRIGGER
statement to enable, disable, or compile a database trigger.
Note:
This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, use theCREATE
TRIGGER
statement with the OR
REPLACE
keywords.See Also:
CREATE TRIGGER for information on creating a trigger
DROP TRIGGER for information on dropping a trigger
Oracle Database Concepts for general information on triggers
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 events system privilege.
Specify the schema containing the trigger. If you omit schema
, then Oracle Database assumes the trigger is in your own schema.
Specify the name of the trigger to be altered.
Specify ENABLE
to enable the trigger. You can also use the ENABLE
ALL
TRIGGERS
clause of ALTER
TABLE
to enable all triggers associated with a table. See ALTER TABLE.
Specify DISABLE
to disable the trigger. You can also use the DISABLE
ALL
TRIGGERS
clause of ALTER
TABLE
to disable all triggers associated with a table.
Specify RENAME
TO
new_name
to rename the trigger. Oracle 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.
Specify COMPILE
to explicitly compile the trigger, whether it is valid or invalid. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.
Oracle Database first recompiles objects upon which the trigger depends, if any of these objects are invalid. If the database recompiles the trigger successfully, then the trigger becomes valid.
During recompilation, the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the REUSE
SETTINGS
clause.
If recompiling the trigger results in compilation errors, then the database returns an error and the trigger remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW
ERRORS
.
Specify DEBUG
to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. Specifying this clause has the same effect as specifying PLSQL_DEBUG
= TRUE
in the compiler_parameters_clause
.
See Also:
Oracle Database Application Developer's Guide - Fundamentals for information on debugging procedures
Oracle Database Concepts for information on how Oracle Database maintains dependencies among schema objects, including remote objects
This clause has the same behavior for a trigger as it does for a function. Please refer to the ALTER
FUNCTION
compiler_parameters_clause.
This clause has the same behavior for a trigger as it does for a function. Please refer to the ALTER
FUNCTION
clause REUSE SETTINGS.
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, Oracle Database enables it automatically. You can subsequently disable the trigger with the following 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 the following statement:
ALTER TRIGGER update_job_history ENABLE;
After you reenable the trigger, Oracle Database fires the trigger whenever an employee's job changes as a result of an UPDATE
statement. 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.