Working with Oracle 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. For more information about the CREATE TRIGGER statement, see "CREATE TRIGGER Statement".

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. 

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 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.

Triggers are procedures that are stored in the database and are implicitly run, or fired, when something happens.

Traditionally, triggers supported the execution of a PL/SQL block when an INSERT, UPDATE, or DELETE occurred on a table or view. Triggers support system and other data events on DATABASE and SCHEMA. Oracle Database also supports the execution of PL/SQL or Java procedures.

This chapter discusses DML triggers, INSTEAD OF triggers, and system triggers (triggers on DATABASE and SCHEMA). Topics include:

• Designing Triggers
• Creating Triggers
• Coding the Trigger Body
• Compiling Triggers
• Modifying Triggers
• Enabling and Disabling Triggers
• Viewing Information About Triggers
• Examples of Trigger Applications
• Responding to System Events through Triggers

Designing Triggers

Use the following guidelines when designing your triggers:

• Use triggers to guarantee that when a specific operation is performed, related actions are performed.
• Do not define triggers that duplicate features already built into Oracle Database. For example, do not define triggers to reject bad data if you can do the same checking through declarative integrity constraints.
• Limit the size of triggers. If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure and call the procedure from the trigger.
• Use triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.
• Do not create recursive triggers. For example, creating an AFTER UPDATE statement trigger on the Emp_tab table that itself issues an UPDATE statement on Emp_tab, causes the trigger to fire recursively until it has run out of memory.
• Use triggers on DATABASE judiciously. They are executed for every user every time the event occurs on which the trigger is created.

How Triggers Are Used

Triggers supplement the standard capabilities of Oracle 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 (when child and parent tables are on different nodes )
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing (Log events)
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views
• Prevent DML operations on a table after regular business hours
• Publish information about database events, user events, and SQL statements to subscribing applications
• Enforce complex business or referential integrity rules that you cannot define with constraints (see "How Triggers and Constraints Differ")

How Triggers and Constraints Differ

Both triggers and constraints can constrain data input, but they differ significantly.

A trigger always applies to new data only. For example, a trigger can prevent a DML statement from inserting a NULL value into a database column, but the column might contain NULL values that were inserted into the column before the trigger was defined or while the trigger was disabled.

A constraint can apply either to new data only (like a trigger) or to both new and existing data. Constraint behavior depends on constraint state.

Constraints are easier to write and less error-prone than triggers that enforce the same rules. However, triggers can enforce some complex business rules that constraints cannot. Oracle strongly recommends that you use triggers to constrain data input only in these situations:

• To enforce referential integrity when child and parent tables are on different nodes of a distributed database
• To enforce complex business or referential integrity rules that you cannot define with constraints

DML Triggers

A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE, INSERT, and UPDATE. To create a trigger that fires in response to a MERGE statement, create triggers on the INSERT and UPDATE statements to which the MERGE operation decomposes.

A DML trigger is either simple or compound.

A simple DML trigger fires at exactly one of these timing points:

Before the triggering statement runs(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.) 

After the triggering statement runs(The trigger is called an AFTER statement trigger or statement-level AFTER trigger.)

Before each row that the triggering statement affects(The trigger is called a BEFORE each row trigger or row-level BEFORE trigger.)

After each row that the triggering statement affects(The trigger is called an AFTER each row trigger or row-level AFTER trigger.)

A compound DML trigger created on a table or editioning view can fire at one, some, or all of the preceding timing points. Compound DML triggers help program an approach where you want the actions that you implement for the various timing points to share common data.

A simple or compound DML trigger that fires at row level can access the data in the row that it is processing. 

An INSTEAD OF DML trigger is a DML trigger created on either a noneditioning view or a nested table column of a noneditioning view.

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

Except in an INSTEAD OF trigger, a triggering UPDATE statement can include a column list. With a column list, the trigger fires only when a specified column is updated. Without a column list, the trigger fires when any column of the associated table is updated.  

Conditional Predicates for Detecting Triggering DML Statement

The triggering event of a DML trigger can be composed of multiple triggering statements. When one of them fires the trigger, the trigger can determine which one by using these conditional predicates:

Conditional Predicate	TRUE if and only if:
INSERTING	An INSERT statement fired the trigger.
UPDATING	An UPDATE statement fired the trigger.
UPDATING ('column')	An UPDATE statement that affected the specified column fired the trigger.
DELETING	A DELETE statement fired the trigger.

A conditional predicate can appear wherever a BOOLEAN expression can appear.

Example 9-1 creates a DML trigger that uses conditional predicates to determine which of its four possible triggering statements fired it.

Example 9-1 Trigger Uses Conditional Predicates to Detect Triggering Statement

CREATE OR REPLACE TRIGGER t
  BEFORE
    INSERT OR
    UPDATE OF salary, department_id OR
    DELETE
  ON employees
BEGIN
  CASE
    WHEN INSERTING THEN
      DBMS_OUTPUT.PUT_LINE('Inserting');
    WHEN UPDATING('salary') THEN
      DBMS_OUTPUT.PUT_LINE('Updating salary');
    WHEN UPDATING('department_id') THEN
      DBMS_OUTPUT.PUT_LINE('Updating department ID');
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
  END CASE;
END;
/

Correlation Names and Pseudorecords

Note:

This topic applies only to triggers that fire at row level—that is, row-level simple DML triggers and compound DML triggers with row-level timing point sections.

A trigger that fires at row level can access the data in the row that it is processing by using correlation names. The default correlation names are OLD,NEW, and PARENT. To change the correlation names, use the REFERENCING clause of the CREATE TRIGGER statement (see "referencing_clause ::=").

If the trigger is created on a nested table in a view (see "dml_event_clause ::="), then OLD and NEW refer to the current row of the nested table, andPARENT refers to the current row of the parent table. If the trigger is created on a table or view, then OLD and NEW refer to the current row of the table or view, and PARENT is undefined.

OLD, NEW, and PARENT are also called pseudorecords, because they have record structure, but are allowed in fewer contexts than records are. The structure of a pseudorecord is table_name%ROWTYPE, where table_name is the name of the table on which the trigger is created (for OLD and NEW) or the name of the parent table (for PARENT).

In the trigger_body of a simple trigger or the tps_body of a compound trigger, a correlation name is a placeholder for a bind variable. Reference the field of a pseudorecord with this syntax:

:pseudorecord_name.field_name

In the WHEN clause of a conditional trigger, a correlation name is not a placeholder for a bind variable. Therefore, omit the colon in the preceding syntax.

Table 9-1 shows the values of OLD and NEW fields for the row that the triggering statement is processing.

Table 9-1 OLD and NEW Pseudorecord Field Values

Triggering Statement	OLD.field Value	NEW.field Value
INSERT	NULL	Post-insert value
UPDATE	Pre-update value	Post-update value
DELETE	Pre-delete value	NULL

The restrictions on pseudorecords are:

• A pseudorecord cannot appear in a record-level operation.

For example, the trigger cannot include this statement:

• :NEW := NULL;
  

• A pseudorecord cannot be an actual subprogram parameter.
  (A pseudorecord field can be an actual subprogram parameter.)
• The trigger cannot change OLD field values.
  Trying to do so raises ORA-04085.
• If the triggering statement is DELETE, then the trigger cannot change NEW field values.
  Trying to do so raises ORA-04084.
• An AFTER trigger cannot change NEW field values, because the triggering statement runs before the trigger fires.
  Trying to do so raises ORA-04084.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS99888 

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm