|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
Note:This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, use the
The trigger must be in your own schema or you must have
TRIGGER system privilege.
In addition, to alter a trigger on
DATABASE, you must have the
ADMINISTER database events system privilege.
See Also:CREATE TRIGGER for more information on triggers based on
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.
ENABLE to enable the trigger. You can also use the
TRIGGERS clause of
TABLE to enable all triggers associated with a table. See ALTER TABLE .
DISABLE to disable the trigger. You can also use the
TRIGGERS clause of
TABLE to disable all triggers associated with a table.
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
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 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
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
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
TRUE in the
Use this clause to specify a value for one of the PL/SQL compiler parameters. The parameters you can specify in this clause are
You can specify each parameter only once in each statement. Each setting is valid only for the current library unit being compiled and does not affect other compilations in this session or system. To affect the entire session or system, you must set a value for the parameter using the
If you omit any parameter from this clause and you specify
REUSE SETTINGS, then if a value was specified for the parameter in an earlier compilation of this library unit, Oracle Database uses that earlier value. If you omit any parameter and either you do not specify
REUSE SETTINGS or no value has been specified for the parameter in an earlier compilation, then the database obtains the value for that parameter from the session environment.
You cannot set a value for the
PLSQL_DEBUG parameter if you also specify
DEBUG, because both clauses set the
PLSQL_DEBUG parameter, and you can specify a value for each parameter only once.
SETTINGS to prevent Oracle from dropping and reacquiring compiler switch settings. With this clause, Oracle preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in this statement.
For backward compatibility, Oracle Database sets the persistently stored value of the
PLSQL_COMPILER_FLAGS initialization parameter to reflect the values of the
PLSQL_DEBUG parameters that result from this statement.
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.
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