Oracle7 Server Concepts

An Introduction to Triggers

Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.

Triggers are similar to stored procedures, discussed in Chapter 14, "Procedures and Packages". A trigger can include SQL and PL/SQL statements to execute as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. While a procedure is explicitly executed by a user, application, or trigger, one or more triggers are implicitly fired (executed) by Oracle when a triggering INSERT, UPDATE, or DELETE statement is issued, no matter which user is connected or which application is being used.

For example, Figure 15 - 1 shows a database application with some SQL statements that implicitly fire several triggers stored in the database.

Figure 15 - 1. Triggers

Notice that triggers are stored in the database separately from their associated tables.

Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.

How Triggers Are Used

In many cases, triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can permit DML operations against a table only if they are issued during regular business hours. The standard security features of Oracle, roles and privileges, govern which users can submit DML statements against the table. In addition, the trigger further restricts DML operations to occur only at certain times during weekdays. This is just one way that you can use triggers to customize information management in an Oracle database.

In addition, triggers are commonly used 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 sophisticated auditing

maintain synchronous table replicates

gather statistics on table access

Examples of many of these different trigger uses are included in the Oracle7 Server Application Developer's Guide.

A Cautionary Note about Trigger Use

When a trigger is fired, a SQL statement within its trigger action potentially can fire other triggers, as illustrated in Figure 15 - 2. When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.

Figure 15 - 2. Cascading Triggers

While triggers are useful for customizing a database, you should only use triggers when necessary. The excessive use of triggers can result in complex interdependences, which may be difficult to maintain in a large application.

Database Triggers vs. Oracle Forms Triggers

Oracle Forms can also define, store, and execute triggers. However, do not confuse Oracle Forms triggers with the database triggers discussed in this chapter.

Database triggers are defined on a table, stored in the associated database, and executed as a result of an INSERT, UPDATE, or DELETE statement being issued against a table, no matter which user or application issues the statement.

Oracle Forms triggers are part of an Oracle Forms application and are fired only when a specific trigger point is executed within a specific Oracle Forms application. SQL statements within an Oracle Forms application, as with any database application, can implicitly cause the firing of any associated database trigger. For more information about Oracle Forms and Oracle Forms triggers, see the Oracle Forms User's Guide.

Triggers vs. Declarative Integrity Constraints

Triggers and declarative integrity constraints can both be used to constrain data input. However, triggers and integrity constraints have significant differences.

A declarative integrity constraint is a statement about the database that is never false while the constraint is enabled. A constraint applies to existing data in the table and any statement that manipulates the table.

Triggers constrain what transactions can do. A trigger does not apply to data loaded before the definition of the trigger. Therefore, it does not guarantee all data in a table conforms to its rules.

A trigger enforces transitional constraints; that is, a trigger only enforces a constraint at the time that the data changes. Therefore, a constraint such as "make sure that the delivery date is at least seven days from today" should be enforced by a trigger, not a declarative integrity constraint.

In evaluating triggers that contain SQL functions that have NLS parameters as arguments (for example, TO_CHAR, TO_DATE, and TO_NUMBER), the default values for these parameters are taken from the NLS parameters currently in effect for the session. You can override the default values by specifying NLS parameters explicitly in such functions when you create a trigger.

For more information about declarative integrity constraints, see Chapter 7, "Data Integrity".