22 Triggers

How Triggers Are Used

Triggers supplement the standard capabilities of Oracle Database to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers to:

• Automatically generate derived column values

• Prevent invalid transactions

• Enforce complex security authorizations

• Enforce referential integrity across nodes in a distributed database

• Enforce complex business rules

• Provide transparent event logging

• Provide auditing

• Maintain synchronous table replicates

• Gather statistics on table access

• Modify table data when DML statements are issued against views

• Publish information about database events, user events, and SQL statements to subscribing applications

This section includes the following topics:

• Some Cautionary Notes about Triggers

• Triggers Compared with Declarative Integrity Constraints

The Triggering Event or Statement

A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following:

• An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)

• A CREATE, ALTER, or DROP statement on any schema object

• A database startup or instance shutdown

• A specific error message or any error message

• A user logon or logoff

For example, in Figure 22-2, the triggering statement is:

... UPDATE OF parts_on_hand ON inventory ...

This statement means that when the parts_on_hand column of a row in the inventory table is updated, fire the trigger. When the triggering event is an UPDATE statement, you can include a column list to identify which columns must be updated to fire the trigger. You cannot specify a column list for INSERT and DELETE statements, because they affect entire rows of information.

A triggering event can specify multiple SQL statements:

... INSERT OR UPDATE OR DELETE OF inventory ...

This part means that when an INSERT, UPDATE, or DELETE statement is issued against the inventory table, fire the trigger. When multiple types of SQL statements can fire a trigger, you can use conditional predicates to detect the type of triggering statement. In this way, you can create a single trigger that runs different code based on the type of statement that fires the trigger.

Trigger Restriction

A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to false or unknown. In the example, the trigger restriction is:

new.parts_on_hand < new.reorder_point 

Consequently, the trigger does not fire unless the number of available parts is less than a present reorder amount.

Trigger Action

A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the SQL statements and code to be run when the following events occur:

• A triggering statement is issued.

• The trigger restriction evaluates to true.

Like stored procedures, a trigger action can:

• Contain SQL, PL/SQL, or Java statements

• Define PL/SQL language constructs such as variables, constants, cursors, exceptions

• Define Java language constructs

• Invoke stored procedures

If the triggers are row triggers, the statements in a trigger action have access to column values of the row being processed by the trigger. Correlation names provide access to the old and new values for each column.

Row Triggers and Statement Triggers

When you define a trigger, you can specify the number of times the trigger action is to be run:

• Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows

• Once for the triggering statement, no matter how many rows it affects

This section includes the following topics:

• Row Triggers

• Statement Triggers

BEFORE and AFTER Triggers

When defining a trigger, you can specify the trigger timing—whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.

BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.

This section includes the following topics:

• BEFORE Triggers

• AFTER Triggers

• Trigger Type Combinations

Compound Triggers

A compound trigger is a single trigger on a table that allows you to specify actions for each of four timing points:

• Before the firing statement

• Before each row that the firing statement affects

• After each row that the firing statement affects

• After the firing statement

The compound trigger body supports a common PL/SQL state that the code for each timing point can access. The common state is automatically destroyed when the firing statement completes, even when the firing statement causes an error.

The effect of the compound trigger is similar to what you could achieve with a simple trigger for each of the timing points for which you needed to code action, with an ancillary package to hold the state that these simple triggers would share. The obvious advantage of the compound trigger is that the required code is managed in a single compilation unit, but the more important advantage is that the lifetime of the compound trigger's state is automatically limited to the duration of the firing statement.

The compound trigger is useful when you want to accumulate facts that characterize the "for each row" changes and then act on them as a body at "after statement" time. Sometimes you are forced to use this approach (to avoid the mutating table error). Sometimes this approach gives better performance; for example, when maintaining a denormalized aggregate value in a master table in response to changes in a detail table, or when maintaining an audit table.

INSTEAD OF Triggers

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are invoked INSTEAD OF triggers because, unlike other types of triggers, Oracle Database fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

This section includes the following topics:

• Modify Views

• Views That Are Not Modifiable

• INSTEAD OF Triggers on Nested Tables

Triggers on System Events and User Events

You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:

• System events

  • Database startup and shutdown

  • Data Guard role transitions

  • Server error message events

• User events

  • User logon and logoff

  • DDL statements (CREATE, ALTER, and DROP)

  • DML statements (INSERT, DELETE, and UPDATE)

Triggers on system events can be defined at the database level or schema level. The DBMS_AQ package is one example of using database triggers to perform certain actions. For example, a database shutdown trigger is defined at the database level:

CREATE TRIGGER register_shutdown 
  ON DATABASE 
  SHUTDOWN 
    BEGIN 
    ...
    DBMS_AQ.ENQUEUE(...); 
    ...
    END; 
    

Triggers on DDL statements or logon/logoff events can also be defined at the database level or schema level. Triggers on DML statements can be defined on a table or view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.

This section includes the following topics:

• Event Publication

• Event Attributes

• System Events

• User Events

The Execution Model for Triggers and Integrity Constraint Checking

When a statement in a trigger body causes another trigger to fire, the triggers are said to be cascading. Oracle Database allows up to 32 triggers to cascade at simultaneously.

A relational database does not guarantee the order of rows processed by a SQL statement. Therefore, do not create triggers that depend on the order in which rows are processed.

Data Access for Triggers

When a trigger is fired, the tables referenced in the trigger action might be currently undergoing changes by SQL statements in other users' transactions. In all cases, the SQL statements running within triggers follow the common rules used for standalone SQL statements. In particular, if an uncommitted transaction has modified values that a trigger being fired either needs to read (query) or write (update), then the SQL statements in the body of the trigger being fired use the following guidelines:

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

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

Storage of PL/SQL Triggers

Oracle Database stores PL/SQL triggers in compiled form, just like stored procedures. When a CREATE TRIGGER statement commits, the compiled PL/SQL code is stored in the database and the source code of the trigger is flushed from the shared pool.

Execution of Triggers

Oracle Database runs a trigger internally using the same steps used for subprogram execution. The only subtle difference is that a user has the right to fire a trigger if he or she has the privilege to run the triggering statement. Other than this, triggers are validated and run the same way as stored subprograms.

Dependency Maintenance for Triggers

Like procedures, triggers depend on referenced objects. Oracle Database automatically manages the dependencies of a trigger on the schema objects referenced in its trigger action. The dependency issues for triggers are the same as those for stored procedures. Triggers are treated like stored procedures. They are inserted into the data dictionary.