Triggers: Usage Information

This topic discusses the use of database triggers with Oracle Express. Triggers are database objects that can be created with PL/SQL code. This topic includes the following topics:


See Also:

  • Oracle Database Concepts for basic information about triggers (in the chapter about server-side programming)

  • Oracle Database SQL Language Reference for information on the CREATE TRIGGER SQL statement


Overview of Triggers

A database trigger is a stored subprogram associated with a database table, view, or event. The trigger can be called once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be called after the event, to record it or take some follow-up action. Or, the trigger can be called before the event to prevent erroneous operations or fix new data so that it conforms to business rules. The executable part of a trigger can contain procedural statements as well as SQL data manipulation statements.

Triggers are created using the SQL CREATE TRIGGER statement. The CREATE (or CREATE OR REPLACE) statement fails if any errors exist in the PL/SQL block.

Types of Triggers

A trigger is either a stored PL/SQL block or a PL/SQL, or C procedure associated with a table, view, schema, or the database itself. Oracle automatically executes a trigger when a specified event takes place, which usually is a DML statement being issued against the table.

You can create triggers to be fired on any of the following:

  • DML statements (DELETE, INSERT, UPDATE)

  • DDL statements (CREATE, ALTER, DROP)

  • Database operations (LOGON, LOGOFF)

Naming Triggers

Trigger names must be unique with respect to other triggers in the same schema. Trigger names do not need to be unique with respect to other schema objects, such as tables, views, and procedures. For example, a table and a trigger can have the same name; however, to avoid confusion, this is not recommended.

When Is the Trigger Fired?

A trigger is fired based on a triggering statement, which specifies:

  • The SQL statement or the system event, database event, or DDL event that fires the trigger body. The options include DELETE, INSERT, and UPDATE. One, two, or all three of these options can be included in the triggering statement specification.

  • The table, view, DATABASE, or SCHEMA associated with the trigger.

If a trigger contained the following statement:

AFTER DELETE OR INSERT OR UPDATE ON employees ...

then any of the following statements would fire the trigger:


DELETE FROM employees WHERE ...;
INSERT INTO employees VALUES ( ... );
INSERT INTO employees SELECT ... FROM ... ;
UPDATE employees SET ... ;

An UPDATE statement might include a list of columns. If a triggering statement includes a column list, the trigger is fired only when one of the specified columns is updated. If a triggering statement omits a column list, the trigger is fired when any column of the associated table is updated. A column list cannot be specified for INSERT or DELETE triggering statements.

Controlling When a Trigger Is Fired

There are several ways to control when a trigger is fired.

Firing Triggers With the BEORE and AFTER Options

The BEFORE or AFTER option in the CREATE TRIGGER statement specifies exactly when to fire the trigger body in relation to the triggering statement that is being run. In a CREATE TRIGGER statement, the BEFORE or AFTER option is specified just before the triggering statement.

In general, you use BEFORE or AFTER triggers to achieve the following results:

  • Use a BEFORE row trigger to modify the row before the row data is written to disk.

  • Use an AFTER row trigger to obtain, and perform operations, using the row Id.


    Note:

    BEFORE row triggers are slightly more efficient than AFTER row triggers. With AFTER row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger.

If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE, then Oracle performs a transparent ROLLBACK and restarts the update. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger is fired again. The rollback does not undo changes to any package variables referenced in the trigger. Your package should include a counter variable to detect this situation.

Firing Triggers With the FOR EACH ROW Option

The FOR EACH ROW option determines whether the trigger is a row trigger or a statement trigger. If you specify FOR EACH ROW, then the trigger fires once for each row of the table that is affected by the triggering statement. These triggers are referred to as row-level triggers.

The absence of the FOR EACH ROW option indicates that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement. These triggers are referred to as statement-level triggers and are useful for performing validation checks for the entire statement.

Firing Triggers Based on Conditions (WHEN Clause)

An optional trigger restriction can be included in the definition of a row trigger by specifying a Boolean SQL expression in a WHEN clause.

If included, then the expression in the WHEN clause is evaluated for each row that the trigger affects. If the expression evaluates to TRUE for a row, then the trigger body is fired on behalf of that row.

The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause. A WHEN clause cannot be included in the definition of a statement trigger.

Accessing Column Values in Row Triggers

Within a trigger body of a row trigger, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement. Two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. These columns in the table are identified by :OLD.colum_name and :NEW.column_name.

Depending on the type of triggering statement, certain correlation names might not have any meaning.

  • A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT, the old values are null.

  • A trigger fired by an UPDATE statement has access to both old and new column values for both BEFORE and AFTER row triggers.

  • A trigger fired by a DELETE statement has meaningful access to :OLD column values only. Because the row no longer exists after the row is deleted, the :new values are NULL and cannot be modified.

Old and new values are available in both BEFORE and AFTER row triggers. A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of NEW.column, then an AFTER row trigger fired by the same statement sees the change assigned by the BEFORE row trigger.

Correlation names can also be used in the Boolean expression of a WHEN clause. A colon (:) must precede the OLD and NEW qualifiers when they are used in a trigger body, but a colon is not allowed when using the qualifiers in the WHEN clause.

Detecting the DML Operation That Fired a Trigger

If more than one type of DML operation can fire a trigger, such as ON INSERT OR UPDATE, the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger.

Within the code of the trigger body, you can execute blocks of code depending on the kind of DML operation fired the trigger.

In an UPDATE trigger, a column name can be specified with an UPDATING conditional predicate to determine if the named column is being updated. For example, assume a trigger is defined as the following:


CREATE OR REPLACE TRIGGER ...
... UPDATE OF salary ON employees ...
BEGIN
... IF UPDATING ('salary') THEN ... END IF;
...

The code in the THEN clause runs only if the triggering UPDATE statement updates the salary column. This way, the trigger can minimize its overhead when the column of interest is not being changed.

Enabled and Disabled Trigger Modes

A trigger can be in enabled or disabled modes. You would disable a trigger if you did not want the trigger to execute, perhaps during maintenance activities on the database.

  • Enabled. An enabled trigger executes its trigger body if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

  • Disabled. A disabled trigger does not execute its trigger body, even if a triggering statement is entered and the trigger restriction (if any) evaluates to TRUE.

Error Conditions and Exceptions in the Trigger Body

If a predefined or user-defined error condition or exception is raised during the execution of a trigger body, then all effects of the trigger body, as well as the triggering statement, are rolled back unless the error is trapped by an exception handler. Therefore, a trigger body can prevent the execution of the triggering statement by raising an exception. User-defined exceptions are commonly used in triggers that enforce complex security authorizations or integrity constraints.

Designing Triggers

This topic discusses the design of triggers. It includes the following topics:

Guidelines For 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. The size of the trigger cannot be more than 32K.

  • 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 employees table that itself issues an UPDATE statement on employees, 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.

Restrictions on Creating Triggers

When creating triggers with PL/SQL code, there are some restrictions that are not required for standard PL/SQL blocks. The following topics discuss these restrictions.

SQL Statements Allowed in Trigger Bodies

The body of a trigger can contain DML SQL statements. It can also contain SELECT statements, but they must be SELECT... INTO... statements or the SELECT statement in the definition of a cursor.

DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger. ROLLBACK, COMMIT, and SAVEPOINT cannot be used.For system triggers, {CREATE/ALTER/DROP} TABLE statements and ALTER...COMPILE are allowed.


Note:

A procedure called by a trigger cannot run the previous transaction control statements, because the procedure runs within the context of the trigger body.

Statements inside a trigger can reference remote schema objects. However, pay special attention when calling remote procedures from within a local trigger. If a timestamp or signature mismatch is found during execution of the trigger, then the remote procedure is not run, and the trigger is invalidated.

System Trigger Restrictions

Only committed triggers are fired. For example, if you create a trigger that should be fired after all CREATE events, then the trigger itself does not fire after the creation, because the correct information about this trigger was not committed at the time when the trigger on CREATE events was fired.

For example, if you execute the following SQL statement:


CREATE OR REPLACE TRIGGER my_trigger
  AFTER CREATE ON DATABASE
BEGIN
  NULL;
END;

Then, trigger my_trigger is not fired after the creation of my_trigger. Oracle Database does not fire a trigger that is not committed.

Privileges Needed to Work with Triggers

To create a trigger in your schema, you must have the CREATE TRIGGER system privilege, and any of the following apply:

  • You own the table specified in the triggering statement, or

  • You have the ALTER privilege for the table in the triggering statement, or

  • You have the ALTER ANY TABLE system privilege.

To create a trigger in another user's schema, or to reference a table in another schema from a trigger in your schema, you must have the CREATE ANY TRIGGER system privilege. With this privilege, the trigger can be created in any schema and can be associated with any user's table. In addition, the user creating the trigger must also have EXECUTE privilege on the referenced procedures, functions, or packages.

To create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER privilege. If this privilege is later revoked, then you can drop the trigger, but not alter it.

The object privileges to the schema objects referenced in the trigger body must be granted to the trigger owner explicitly (not through a role). The statements in the trigger body operate under the privilege domain of the trigger owner, not the privilege domain of the user issuing the triggering statement. This is similar to the privilege model for stored procedures.