|Oracle® Database PL/SQL Language Reference
12c Release 1 (12.1)
|PDF · Mobi · ePub|
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
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
TRIGGER system privilege.
In addition, to alter a trigger on
DATABASE, you must have the
TRIGGER system privilege.
See Also:"CREATE TRIGGER Statement" for more information about triggers based on
Name of the schema containing the trigger. Default: your schema.
Name of the trigger to be altered.
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
Has the same behavior for a trigger as it does for a function. See "DEBUG".
See Also:Oracle Database Development Guide for information about debugging a trigger using the same facilities available for stored subprograms
Has the same behavior for a trigger as it does for a function. See REUSE SETTINGS.
Has the same behavior for a trigger as it does for a function. See the
Enables or disables the trigger.
Renames the trigger without changing its state.
When you rename a trigger, the database rebuilds the remembered source of the trigger in the
DBA_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.
Specifies whether the trigger becomes an editioned or noneditioned object if editioning is later enabled for the schema object type
EDITIONABLE. For information about altering editioned and noneditioned objects, see Oracle Database Development Guide.
Disabling Triggers: Example 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.
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