1. Database PL/SQL Language Reference
  2. PL/SQL Triggers
  3. Overview of Triggers

Overview of Triggers

Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it.

While a trigger is enabled, the database automatically invokes it—that is, the trigger fires—whenever its triggering event occurs. While a trigger is disabled, it does not fire.

You create a trigger with the CREATE TRIGGER statement. You specify the triggering event in terms of triggering statements and the item on which they act. The trigger is said to be created on or defined on the item, which is either a table, a view, a schema, or the database. You also specify the timing point, which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. By default, a trigger is created in the enabled state.

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger.

A crossedition trigger is a DML trigger for use only in edition-based redefinition.

If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger.

A conditional trigger is a DML or system trigger that has a WHEN clause that specifies a SQL condition that the database evaluates for each row that the triggering statement affects.

When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other users' transactions. SQL statements running in triggers follow the same rules that standalone SQL statements do. Specifically:

  • Queries in the trigger see the current read-consistent materialized view of referenced tables and any data changed in the same transaction.

  • Updates in the trigger wait for existing data locks to be released before proceeding.

An INSTEAD OF trigger is either:

  • A DML trigger created on either a noneditioning view or a nested table column of a noneditioning view

  • A system trigger defined on a CREATE statement

The database fires the INSTEAD OF trigger instead of running the triggering statement.

Note:

A trigger is often called by the name of its triggering statement (for example, DELETE trigger or LOGON trigger), the name of the item on which it is defined (for example, DATABASE trigger or SCHEMA trigger), or its timing point (for example, BEFORE statement trigger or AFTER each row trigger).

See Also: