Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E25519-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub
LNPLS99996

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.

LNPLS1890Topics

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

LNPLS1410alter_trigger ::=

Description of alter_trigger.gif follows
Description of the illustration alter_trigger.gif

LNPLS1411compiler_parameters_clause ::=

Description of compiler_parameters_clause.gif follows
Description of the illustration compiler_parameters_clause.gif

Semantics

LNPLS1412schema

Name of the schema containing the trigger. Default: your schema.

LNPLS1413trigger

Name of the trigger to be altered.

LNPLS1414ENABLE

Enables the trigger.

LNPLS1891DISABLE

Disables the trigger.

LNPLS1415RENAME 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 USER_SOURCE, ALL_SOURCE, and 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.

LNPLS1892COMPILE

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 REUSE SETTINGS.

LNPLS1893DEBUG

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

See Also:

Oracle Database Advanced Application Developer's Guide for information about debugging a trigger using the same facilities available for stored subprograms

LNPLS1419REUSE SETTINGS

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

LNPLS1418compiler_parameters_clause

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

Examples

LNPLS1420Disabling 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.

LNPLS1421Enabling 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

LNPLS1894In this chapter:

LNPLS1895In other chapters:

Reader Comment

   

Comments, corrections, and suggestions are forwarded to authors every week. By submitting, you confirm you agree to the terms and conditions. Use the OTN forums for product questions. For support or consulting, file a service request through My Oracle Support.

Hide Navigation

Quick Lookup

Database Library · Master Index · Master Glossary · Book List · Data Dictionary · SQL Keywords · Initialization Parameters · Advanced Search · Error Messages

Main Categories

This Document

New and changed documents:
RSS Feed HTML RSS Feed PDF