|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
|PDF · Mobi · ePub|
Database triggers are procedures that are stored in the database and activated ("fired") when specific conditions occur, such as adding a row to a table. You can use triggers to supplement the standard capabilities of the database to provide a highly customized database management system. For example, you can create a trigger to restrict DML operations against a table, allowing only statements issued during regular business hours.
Database triggers can be associated with a table, schema, or database. They are implicitly fired when:
DML statements are executed (
DELETE) against an associated table
Certain DDL statements are executed (for example:
DROP) on objects within a database or schema
A specified database event occurs (for example:
This is not a complete list. See the Oracle Database SQL Language Reference for a full list of statements and database events that cause triggers to fire
Create triggers with the
CREATE TRIGGER statement. They can be defined as firing
AFTER the triggering event, or
INSTEAD OF it. The following statement creates a trigger
scott.emp_permit_changes on table
scott.emp. The trigger fires before any of the specified statements are executed.
CREATE TRIGGER scott.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON scott.emp . . . pl/sql block . . .
You can later remove a trigger from the database by issuing the
DROP TRIGGER statement.
A trigger can be in either of two distinct modes:
An enabled trigger executes its trigger body if a triggering statement is issued and the trigger restriction, if any, evaluates to true. By default, triggers are enabled when first created.
A disabled trigger does not execute its trigger body, even if a triggering statement is issued and the trigger restriction (if any) evaluates to true.
To enable or disable triggers using the
ALTER TABLE statement, you must own the table, have the
ALTER object privilege for the table, or have the
ALTER ANY TABLE system privilege. To enable or disable an individual trigger using the
ALTER TRIGGER statement, you must own the trigger or have the
ALTER ANY TRIGGER system privilege.
ALTER TRIGGER reorder ENABLE;
To enable all triggers defined for a specific table, use the
ALTER TABLE statement with the
ENABLE ALL TRIGGERS option. To enable all triggers defined for the
INVENTORY table, enter the following statement:
ALTER TABLE inventory ENABLE ALL TRIGGERS;
See Also:Oracle Database SQL Language Reference for syntax and other information about the
An object that the trigger references is not available.
You must perform a large data load and want it to proceed quickly without firing triggers.
You are loading data into the table to which the trigger applies.
ALTER TRIGGER reorder DISABLE;
You can disable all triggers associated with a table at the same time using the
ALTER TABLE statement with the
DISABLE ALL TRIGGERS option. For example, to disable all triggers defined for the
inventory table, enter the following statement:
ALTER TABLE inventory DISABLE ALL TRIGGERS;