Skip Headers

Oracle® Database Concepts
1 0g Release 1 (10.1)
Part Number B10743-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 < img src="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0" />
Index		 Go to Master Index< br /> Master Index
Feedback
Go to previous page
Previ ous		 Go to next pag e
Next
 View PDF

22 Triggers

This chapter discu sses triggers, which are procedures stored in PL/SQL or Java that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.

This chapter contains the following topics:

Introduction to Triggers

You can write triggers that f ire whenever one of the following operations occurs:

  1. DML statements (INSERT, U PDATE, DELETE) on a particular table or view, issued by any user

  2. DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database

  3. < p>Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema /user in the database

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL o r Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Figure 22-1 shows a database application with some SQL statements that implicitly fire several triggers stored in the database. Notice that the database stores triggers separately from their associated tables.

Figu re 22-1 Triggers

Description of cncpt076.gif follows
Description of the illustration cncpt076.gif

A trigger can also call out to a C procedure, which is useful for computationally intensive operations.

The even ts that fire a trigger include the following:

  • DML statements that modify data in a table (INSERT, UPDATE, or DELETE)

  • DDL statements

  • System e vents such as startup, shutdown, and error messages

  • User events such as logon and logoff


    < p>Note:

    Oracle Forms can define, store, and run triggers of a different sort. However, do not confuse Oracle Forms triggers with the triggers discussed in this chapter.


    See Also:


How Triggers Are Used

Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For ex ample, 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 transacti ons

  • Enforce complex security authorizations

  • Enforce referential integrity a cross nodes in a distributed database

  • Enforce complex business rules

  • Provid e transparent event logging

  • Provide auditing

  • Maintain synchronous table rep licates

  • Gather statistics on table access

  • Modify table data when DML statem ents are issued against views

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


    See Also:

    Oracle Database Application Developer's Guide - Fundamentals for examples of trigger uses

< div class="sect3">

Some Cauti onary Notes about Triggers

Although triggers are useful for customizing a dat abase, use them only when necessary. Excessive use of triggers can result in complex interdependencies, which can be difficult to mai ntain in a large application. For example, when a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers. This can produce unintended effects. Figure 22-2 illustrates cascading triggers.

Figure 22-2 Cascading Triggers

Description of cncpt077.gif follows
Descri ption of the illustration cncpt077.gif

Triggers Compared with Dec larative Integrity Constraints

You c an use both triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle strongly recommends that you use triggers to constrain data input only in the following situations:

  • To enforce referential i ntegrity when child and parent tables are on different nodes of a distributed database

  • To enforce comple x business rules not definable using integrity constraints

  • When a required referential integrity rule ca nnot be enforced using the following integrity constraints:

Parts of a Trigger

A trigger has three basic part s:

  • A triggering event or statement

  • A trigger restriction

  • A trigger action

Figure 22-3 represents each of these parts of a trigger and is n ot meant to show exact syntax. The sections that follow explain each part of a trigger in greater detail.

Figure 22-3 The REORDER Trigger

Description of cncpt078.gif follows
Description of the illustration cncpt078.gif

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 < a id="sthref3195" name="sthref3195">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 logo n or logoff

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

... UPDATE OF parts_on_hand ON inventory ...

This statement means that when the parts_on_hand colum n of a row in the inventory table is updated, fire the trigger. When the triggering event is an UPDATE stat ement, 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 inven tory 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 s tatement that fires the trigger.

< font face="arial, helvetica, sans-serif" color="#330099">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

Consequ ently, 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 tr iggering statement is issued.

  • The trigger restriction evaluates to true.

< a id="sthref3203" name="sthref3203">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

  • Call stored procedures

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

    Types of Triggers< /font>

    This section describes the different types of triggers:

    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 state ment, no matter how many rows it affects

    Row Triggers

    A row trigg er is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement up dates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggeri ng statement affects no rows, a row trigger is not run.

    Row triggers are useful if the code in the trigger action depends on d ata provided by the triggering statement or rows that are affected. For example, Figure 22-3 illustrates a ro w trigger that uses the values of each row affected by the triggering statement.

    Statement Triggers

    A < strong>statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

    Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a stat ement trigger to:

    • Make a complex security check on the current time or user

    • Generate a single audit record

    < /a>

    BEFO RE and AFTER Triggers

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

    BEFORE and AFTER triggers fi red 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 AFT ER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.


    See Also:


    BEFORE Triggers

    BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the followin g situations:

    • When the trigger action determines whether the triggering statement should be allowed to c omplete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement a nd its eventual rollback in cases where an exception is raised in the trigger action.

    • To derive specific column values before completing a triggering INSERT or UPDATE statement.

    AFTER Triggers

    AFTER triggers r un the trigger action after the triggering statement is run.

    Trigger Type Co mbinations

    Using the options listed previously, you can create four types of row and statement triggers:

    • BEFORE statement trigger

      Before executing the trig gering statement, the trigger action is run.

    • BEFORE row trigger

      Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the t rigger action is run, if the trigger restriction was not violated.

    • AFTER st atement trigger

      After executing the triggering statement and applying any deferred integrity co nstraints, the trigger action is run.

    • AFTER row trigg er

      After modifying each row affected by the triggering statement and possibly applying appropriate integrity constrai nts, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

    You can have multip le triggers of the same type for the same statement for any given table. For example, you can have two BEFORE statement triggers for UPDATE statements on the employees table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle materialized view logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger.

    You can create as many triggers of the pr eceding different types as you need for each type of DML statement, (INSERT, UPDATE, or DELETE ).


    See Also:

    Oracle Database Application Developer's Guide - Fundamentals for examples of trigger applications

    INSTEAD OF Triggers

    INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DM L statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle 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.

    Modify Views

    Modifying views can have ambiguous results:

    • Deleting a row in a view could either mean deleting it fro m the base table or updating some values so that it is no longer selected by the view.

    • Inserting a row i n a view could either mean inserting a new row into the base table or updating an existing row so that it is projected by the view.

    • Updating a column in a view that involves joins might change the semantics of other columns that are not projected by the view.

    Object views present additional problems. For exampl e, a key use of object views is to represent master/detail relationships. This operation inevitably involves joins, but modifying joi ns is inherently ambiguous.

    As a result of these ambiguities, there are many restrictions on which views are modifiable. An INSTEAD OF trigger can be used on object views as well as relational views that are not otherwise modifiable.

    A view is inherently modifiable if data can be inserted, updated, or deleted without using INSTEAD OF triggers and if it conforms to the restrictions listed as follows. Even if the vie w is inherently modifiable, you might want to perform validations on the values being inserted, updated or deleted. INSTEAD OF triggers can also be used in this case. Here the trigger code performs the validation on the rows being modified and if valid, propagate the changes to the underlying tables.

    INSTEAD OF triggers also enable you to modify object view instances on the client-side through OCI. To modify an object materialized by an object view in the client-side o bject cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the obj ect view is inherently modifiable. However, it is not necessary to define these triggers for just pinning and reading the view object in the object cache.

    Views That Are Not Modifiable

    If the view query contains any of the following constructs, the view is not inherently modifiable and you therefore cannot perform inserts, updates, or deletes on the view:

    • Set operators

    • Aggregate functions

    • GROUP BY, CONNECT BY, or START WITH clauses

    • The DISTINCT operator

    • Joins (however, some join vie ws are updatable)

    If a view contains pseudocolumns or expressions, you can only update the view with an UPDATE st atement that does not refer to any of the pseudocolumns or expressions.


    < table class="notealso" summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" bord er="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0">

    See Also:

    "Updatable Join Views "

    INSTEAD OF Triggers on Nested Tables

    < p>You cannot modify the elements of a nested table column in a view directly with the TABLE clause. However, you can do so by defining an INSTEAD OF trigger on the nested table column of the view. The triggers on the nested tab les fire if a nested table element is updated, inserted, or deleted and handle the actual modifications to the underlying 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

      • Server error message events

    • User events

      • User logon a nd logoff

      • DDL statements (CREATE, ALTER, and DROP)

      • DML statements (INSERT, DELETE, and UPDATE)

    Trig gers 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 leve l. 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 t rigger defined at the schema or table level fires only when the triggering event involves that schema or table.

    Event Publication

    Event publication uses the publish-subscribe mechanism of Oracle Streams Advanced Queuing. A queue serves as a message repository for subjects of interest to various subs cribers. Triggers use the DBMS_AQ package to enqueue a message when specific system or user events occur.


    See Also:

    See Also:


    System Events

    System events that can fire triggers are related to instance startup and shutdown and error messages. Triggers created on startup and shutdown eve nts have to be associated with the database. Triggers created on error events can be associated with the database or with a schema.

    User Events

    User events that can fire triggers are related to user logon and logoff, DDL statements, and DML statements.

    Triggers on LO GON and LOGOFF Events

    LOGON and LOGOFF triggers can be associated with the database or with a schema. Their attributes include the system event and user name, and they can specify simple conditions on USERID and USERNAME.

    Triggers on DDL Statements

    DDL triggers can be associated with the database or with a schema. Their attributes include the system event, the type of schema object, and its name. They can specify simple conditions on the type an d name of the schema object, as well as functions like USERID and USERNAME. DDL triggers include the follow ing types of triggers:

    Triggers on DML Statements

    DML triggers for event publication are associated with a table. They can be either BEFORE or < code>AFTER triggers that fire for each row on which the specified DML operation occurs. You cannot use INSTEAD OF triggers on views to publish events related to DML statements—instead, you can publish events using BEFORE or AFTER triggers for the DML operations on a view's underlying tables that are caused by INSTEAD < code>OF triggers.

    The attributes of DML triggers for event publication include the system event and the columns defined by the user in the SELECT list. They can specify simple conditions on the type and name of the schema object, as well a s functions (such as UID, USER, USERENV, and SYSDATE), pseudocolumns, and columns . The columns can be prefixed by :OLD and :NEW for old and new values. Triggers on DML statements include t he following triggers:

    Trigger Execution

    A tr igger is in either of two distinct modes:

    Trigger Mod e Definition
    Enabled An enabled trigger runs its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to true.
    Disabled A disabled tri gger does not run its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to true.

    For enabled triggers, Oracle automatically performs the following actions:

    The Execution Model for Triggers and Integrity Constraint Checking

    A single SQL statement can potentially fire up to four types of triggers:

    A tri ggering statement or a statement within a trigger can cause one or more integrity constraints to be checked. Also, triggers can conta in statements that cause other triggers to fire (cascading triggers).

    Oracle uses the following execution model to maintain th e proper firing sequence of multiple triggers and constraint checking:

    1. Run all BEFORE statement triggers that apply to the statement.

    2. Loop for each row affected by the SQL statem ent.

      1. Run all BEFORE row triggers that apply to the statement.

        2. < li>

        Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)

      2. Run all AFTER row triggers that apply to the statement.

    3. Complete deferred integrity constraint checking.

    4. Run all AFTER statement trigg ers that apply to the statement.

    The definition of the execution model is recursive. For example, a given SQL statem ent can cause a BEFORE row trigger to be fired and an integrity constraint to be checked. That BEFORE row trigger, in turn, might perform an update that causes an integrity constraint to be checke d and an AFTER statement trigger to be fired. The AFTER statement trigger causes an integrity constraint to be checked. In this case, the execution model runs the steps recursively, as follows:

    Original SQL statement issued.

    1. BEFORE row triggers fired.

      1. AFTER statement triggers fired by UPDATE in BEFORE row trigger.

        i. Statements of AFTER statement triggers run.

        ii. Integrity constraint checked on tables changed by AFTER statement triggers.

      2. Statements of BEFORE row triggers run.

      3. Integrity constraint checked on tables changed by BEFORE row triggers.

    2. SQL statement run.

    3. Int egrity constraint from SQL statement checked.

    There are two exceptions to this recursion:

    • When a triggering statement modifies one table in a referential constraint (either the primary key or foreign key table), and a tr iggered statement modifies the other, only the triggering statement will check the integrity constraint. This allows row triggers to enhance referential integrity.

    • Statement triggers fired due to DELETE CASCADE and DELETE SET NULL are fired before and after the user DELETE statement, not bef ore and after the individual enforcement statements. This prevents those statement triggers from encountering mutating errors.

    An important property of the execution m odel is that all actions and checks done as a result of a SQL statement must succeed. If an exception is raised within a trigger, and the exception is not explicitly handled, all actions performed as a result of the original SQL statement, including the actions perf ormed by fired triggers, are rolled back. Thus, integrity constraints cannot be compromised by triggers. The execution model takes in to account integrity constraints and disallows triggers that violate declarative integrity constraints.

    For example, in the pr eviously outlined scenario, suppose that the integrity constraint is violated. As a result of this violation, all changes made by the SQL statement, the fired BEFORE row trigger, and the fired AFTER statement trigger are rolled back.


    Note:

    Although triggers of different types are fired in a specific order, triggers of the same type for the same stateme nt are not guaranteed to fire in any specific order. For example, all BEFORE row triggers for a si ngle UPDATE statement may not always fire in the same order. Design your applications so they do not rely on the firing order of multiple triggers of the same type.

    Data Access for Triggers

    When a trigger i s fired, the tables referenced in the trigger action might be currently undergoing changes by SQL statements in other users' transact ions. In all cases, the SQL statements run 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 th e SQL statements in the body of the trigger being fired use the following guidelines:

    • Queries see the cu rrent 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 Tri ggers

    Oracle stores PL/SQL triggers in compile d form, just like stored procedures. When a CREATE TRIGGER stateme nt commits, the compiled PL/SQL code, called P code (for pseudocode), is stored in the database and the source code of the trigger is flushed from the shared pool.


    See Also :

    PL/SQL User's Guide and Reference for more information about compiling and storing PL/SQL code

    < /div>

    Execution of Triggers

    Oracle runs a trigger internally using the same steps used for procedure 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 va lidated and run the same way as stored procedures.


    See Also:

    PL/SQL User's Guide an d Reference for more information about stored procedures

    Dependency Maintenance for Triggers

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

    Go to previous page
    Previous    		 Go to next page
    Next
    		 Oracle
    Copyright © 1993, 2003 Oracle Corporation
    All Rights Reserved.
    G o 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
    Feedback