Day 11

Writing Database
Triggers

by Jonathan Gennick

Today's lesson discusses database triggers. A trigger is used to write procedural logic that is invoked in response to specific data manipulation events. Creative application of database triggers will enable you to accomplish many useful things that otherwise would be impossible. Examples of what you can do with triggers include replicating data, storing data redundantly to avoid frequent table joins, and enforcing complex business rules.

What Is a Trigger?

A trigger is a PL/SQL block that is associated with a table, stored in a database, and executed in response to a specific data manipulation event. Triggers can be executed, or fired, in response to the following events:

• A row is inserted into a table.
• A row in a table is updated.
• A row in a table is deleted.

It is not possible to define a trigger to fire when a row is selected.

A trigger definition consists of these basic parts:

• The event that fires the trigger
• The database table on which the event must occur
• An optional condition controlling when the trigger is executed
• A PL/SQL block containing the code to be executed when the trigger is fired

A trigger is a database object, like a table or an index. When you define a trigger, it becomes part of the database and is always executed when the event for which it is defined occurs. It doesn't matter if the event is triggered by someone typing in a SQL statement using SQL*Plus, running a client/server program that updates the database, or running a utility like Oracle's SQL*Loader in order to bulk-load data. Because of this, triggers serve as a choke point, allowing you to perform critical validation or computations in response to database changes, no matter what the source.

An Example of a Trigger

Suppose for a moment that you wanted to be sure that all department names were stored using uppercase letters. Perhaps you are doing this to facilitate searching on that field. Listing 11.1 shows one way to do this with a trigger.

INPUT/OUTPUT
Listing 11.1. Example of a trigger.

 1: CREATE OR REPLACE TRIGGER department_insert_update
 2:   BEFORE INSERT OR UPDATE ON department
 3:   FOR EACH ROW
 4: DECLARE
 5:   dup_flag  INTEGER;
 6: BEGIN
 7:   --Force all department names to uppercase.
 8: :NEW.dept_name := UPPER(:NEW.dept_name);
 9: END;
10: /
11: Trigger created.

ANALYSIS
Line 1 tells Oracle to create this trigger with the name department_insert_update and to replace any existing trigger of the same name if necessary. Line 2 says that it will be fired whenever a new row is inserted into the department table or whenever a department record is changed. In line 8 there is one line of code that uses the built-in UPPER

function to force the department name to uppercase. Notice the reference to :NEW. This is the default alias for the new value of the record. The alias :OLD can be used to refer to the old value of a field, before an update takes effect. Line 3 tells Oracle to fire this trigger once for each row modified. If you were to issue an UPDATE statement to change the names of all departments in the table, this trigger would be fired for each one of those records.

To demonstrate the effect of this trigger, try issuing the input statements shown in List-
ing 11.2.

INPUT/
OUTPUT
Listing 11.2. Testing the department_insert_update trigger.

 1: INSERT INTO department (dept_id, dept_name) VALUES (10,'payroll');
 2: 1 row created.
 3: INSERT INTO department (dept_id, dept_name) VALUES (11,'Sewage');
 4: 1 row created.
 5: UPDATE department SET dept_name = `Payroll' WHERE dept_id = 10;
 6: 1 row updated.
 7: SELECT dept_id, dept_name FROM department WHERE dept_id BETWEEN 10 AND 11;
 8:   DEPT_ID DEPT_NAME
 9:  -------- --------------------------------
10:        10 PAYROLL
11:        11 SEWAGE

ANALYSIS
Note that the trigger has forced all department names to uppercase regardless of whether the name was the result of a new record inserted or an existing record that was updated.

Types of Triggers

Database triggers can be classified in two different ways: by when they fire in relation to the triggering SQL statement, and by whether or not they fire for each row affected by the triggering SQL statement. This results in four basic trigger types.

NEW TERM
There are two choices for when a trigger fires in relation to a SQL statement, either before or after. Before triggers are executed before the triggering SQL statement. After triggers are executed following the triggering SQL statement.

NEW TERM
A trigger is either a row-level trigger or a statement-level trigger. A row-level trigger executes once for each row affected by the triggering SQL statement, whereas a statement-level trigger is executed only once. Only row-level triggers have access to the data values in the affected records. Statement-level triggers do not. This is because SQL is a set-oriented language—SQL statements can affect many or even all rows in a table. Statement-level triggers are only fired once, so it would not be possible to resolve a column reference in such a trigger.

The possible combinations of the choices result in the four basic trigger types listed in
Table 11.1.

Table 11.1. The four basic trigger types.

Triggers execute in response to a SQL statement and can be defined for the INSERT, UPDATE, and DELETE statements. These are often referred to as insert triggers, update triggers, and delete triggers, respectively. Together with the four basic types from Table 11.1, this gives a total of 12 possible trigger types, which are listed in Table 11.2. Note that the SELECT statement is the only data manipulation statement for which no triggers can be defined.

Table 11.2. The 12 trigger types.