Using Triggers
This chapter contains
About Triggers
Creating Triggers
Changing Triggers
Disabling and Enabling Triggers
About Trigger Compilation and Dependencies
Dropping Triggers
About Triggers
A trigger is a PL/SQL unit that is stored in the database and (if it is in the enabled state) automatically
executes ("fires") in response to a specified event.
A trigger has this structure:
TRIGGER trigger_name
triggering_event
[ trigger_restriction ]
BEGIN
triggered_action;
END;
The trigger_name must be unique for triggers in the schema. A trigger can have the same name as
another kind of object in the schema (for example, a table); however, Oracle recommends using a
naming convention that avoids confusion.
If the trigger is in the enabled state, the triggering_event causes the database to execute the
triggered_action if the trigger_restriction is either TRUE or omitted. The triggering_event is associated
with either a table, a view, a schema, or the database, and it is one of these:
DML statement (described in "About Data Manipulation Language (DML) Statements")
DDL statement (described in "About Data Definition Language (DDL) Statements")
Database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
If the trigger is in the disabled state, the triggering_event does not cause the database to execute the
triggered_action, even if the trigger_restriction is TRUE or omitted.
, By default, a trigger is created in the enabled state. You can disable an enabled trigger, and enable a
disabled trigger.
Unlike a subprogram, a trigger cannot be invoked directly. A trigger is invoked only by its triggering
event, which can be caused by any user or application. You might be unaware that a trigger is executing
unless it causes an error that is not handled properly.
A simple trigger can fire at exactly one of these timing points:
Before the triggering event executes (statement-level BEFORE trigger)
After the triggering event executes (statement-level AFTER trigger)
Before each row that the event affects (row-level BEFORE trigger)
After each row that the event affects (row-level AFTER trigger)
A compound trigger can fire at multiple timing points. For information about compound triggers, see
Oracle Database PL/SQL Language Reference.
An INSTEAD OF trigger is defined on a view, and its triggering event is a DML statement. Instead of
executing the DML statement, Oracle Database executes the INSTEAD OF trigger. For more information,
see "Creating an INSTEAD OF Trigger".
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each
event associated with the owner of the schema (the current user). A trigger defined on a database fires
for each event associated with all users.
One use of triggers is to enforce business rules that apply to all client applications. For example, suppose
that data added to the EMPLOYEES table must have a certain format, and that many client applications
can add data to this table. A trigger on the table can ensure the proper format of all data added to it.
Because the trigger executes whenever any client adds data to the table, no client can circumvent the
rules, and the code that enforces the rules can be stored and maintained only in the trigger, rather than
in every client application. For other uses of triggers, see Oracle Database PL/SQL Language Reference.
Creating Triggers
To create triggers, use either the SQL Developer tool Create Trigger or the DDL statement CREATE
TRIGGER. This section shows how to use both of these ways to create triggers.
By default, a trigger is created in the enabled state. To create a trigger in disabled state, use the CREATE
TRIGGER statement with the DISABLE clause.
About OLD and NEW Pseudorecords
This chapter contains
About Triggers
Creating Triggers
Changing Triggers
Disabling and Enabling Triggers
About Trigger Compilation and Dependencies
Dropping Triggers
About Triggers
A trigger is a PL/SQL unit that is stored in the database and (if it is in the enabled state) automatically
executes ("fires") in response to a specified event.
A trigger has this structure:
TRIGGER trigger_name
triggering_event
[ trigger_restriction ]
BEGIN
triggered_action;
END;
The trigger_name must be unique for triggers in the schema. A trigger can have the same name as
another kind of object in the schema (for example, a table); however, Oracle recommends using a
naming convention that avoids confusion.
If the trigger is in the enabled state, the triggering_event causes the database to execute the
triggered_action if the trigger_restriction is either TRUE or omitted. The triggering_event is associated
with either a table, a view, a schema, or the database, and it is one of these:
DML statement (described in "About Data Manipulation Language (DML) Statements")
DDL statement (described in "About Data Definition Language (DDL) Statements")
Database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)
If the trigger is in the disabled state, the triggering_event does not cause the database to execute the
triggered_action, even if the trigger_restriction is TRUE or omitted.
, By default, a trigger is created in the enabled state. You can disable an enabled trigger, and enable a
disabled trigger.
Unlike a subprogram, a trigger cannot be invoked directly. A trigger is invoked only by its triggering
event, which can be caused by any user or application. You might be unaware that a trigger is executing
unless it causes an error that is not handled properly.
A simple trigger can fire at exactly one of these timing points:
Before the triggering event executes (statement-level BEFORE trigger)
After the triggering event executes (statement-level AFTER trigger)
Before each row that the event affects (row-level BEFORE trigger)
After each row that the event affects (row-level AFTER trigger)
A compound trigger can fire at multiple timing points. For information about compound triggers, see
Oracle Database PL/SQL Language Reference.
An INSTEAD OF trigger is defined on a view, and its triggering event is a DML statement. Instead of
executing the DML statement, Oracle Database executes the INSTEAD OF trigger. For more information,
see "Creating an INSTEAD OF Trigger".
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each
event associated with the owner of the schema (the current user). A trigger defined on a database fires
for each event associated with all users.
One use of triggers is to enforce business rules that apply to all client applications. For example, suppose
that data added to the EMPLOYEES table must have a certain format, and that many client applications
can add data to this table. A trigger on the table can ensure the proper format of all data added to it.
Because the trigger executes whenever any client adds data to the table, no client can circumvent the
rules, and the code that enforces the rules can be stored and maintained only in the trigger, rather than
in every client application. For other uses of triggers, see Oracle Database PL/SQL Language Reference.
Creating Triggers
To create triggers, use either the SQL Developer tool Create Trigger or the DDL statement CREATE
TRIGGER. This section shows how to use both of these ways to create triggers.
By default, a trigger is created in the enabled state. To create a trigger in disabled state, use the CREATE
TRIGGER statement with the DISABLE clause.
About OLD and NEW Pseudorecords
