Previous Table of Contents Next


Database Triggers

Database triggers are objects that are closely associated with tables. There are four distinct times at which database triggers fire, twice at the statement level and twice at the row level, as shown in Table 2.5.

Table 2.5 When database triggers fire.

Trigger Type Fires
before The statement level trigger fires before a statement is executed on the trigger’s associated table.
after The statement level trigger fires after a statement is executed on the trigger’s associated table.
before row The row level trigger fires once for each row affected by a statement executed on the trigger’s associated table. The trigger fires prior to the execution of the statement and can alter the contents of the data.
after row The row level trigger fires once for each row affected by a statement executed on the trigger’s associated table. The trigger fires following the execution of the statement.

Database triggers can fire on INSERT, UPDATE, and delete events. Thus, there are 12 possible triggers that can be written for a table. The types of database triggers execute in a specific order, as illustrated in Figure 2.11.


Figure 2.11  The execution order of database triggers.

Database triggers are covered in more detail in Chapters 7.

Stored Procedures And Functions

While database triggers allow the enforcement of complex business rules at the database level, they fall short in a number of ways. First, there is no way to explicitly call a database trigger without altering one or more rows in the trigger’s associated table. Second, the code is tightly restricted in its role, because by nature Oracle7’s triggers are very specific to data.

Stored procedures and functions don’t suffer from these shortcomings (nor do they have the value associated with being closely tied to a table). Instead, these objects stand alone in the database and are referenced by triggers and other stored procedures. These objects allow developers to write applications at the database level that are truly modular.

The only real difference between a stored procedure and a stored function is that a function must explicitly return a value using a RETURN statement. Listing 2.18 shows a typical stored procedure, and Listing 2.19 shows a typical stored function.

Listing 2.18 A typical stored procedure.

CREATE OR REPLACE
PROCEDURE Update_Student_GPA (SSN IN    number)

IS

    <variable declarations>

BEGIN
    <statements>
END Update_Student_GPA;

Listing 2.19 A typical stored function.

CREATE OR REPLACE
FUNCTION Get_Student_GPA (SSN IN     number) RETURN number

IS
    <variable declarations>

BEGIN
    <statements>
    <RETURN statement>
END Get_Student_GPA;

You can execute stored procedures and functions as follows:

Procedure:

Update_Student_GPA (SSN => 999999999);

Function:

nSSN := Get_Student_GPA (SSN => 999999999);

Notice the output of the function is assigned to a variable. This is the only way a stored function can be called, other than from inside a DML statement.

Parameters

Stored procedures and functions can accept parameters from their calling application. There are three types of parameters: IN, OUT, and IN OUT.

  IN parameters—Pass a value into the procedure or function. This value cannot be altered within the procedure.
  OUT parameters—Pass a NULL value into the procedure or function, but can be altered by the procedure to return a value to the calling application via the OUT parameter.
  IN OUT parameters—Pass a value into the procedure or function, which can then be modified by the procedure so that a different value is returned to the calling application via the parameter.

There are two methods of referencing parameters in procedure and function calls: positional and named. The positional method of calling a procedure takes the following form:

Update_Student_GPA (999999999);

The named method of calling a procedure takes the following form:

Update_Student_GPA (ssn => 999999999);

Positional notation assumes that the first value passed to the procedure or function corresponds to the first parameter, the second value to the second parameter, and so forth. Named notation allows the parameters to be specified in any order, because the developer must specify which parameter a value is intended to fill.

It is possible to mix both named and positional notation in a procedure or function call, but once named notation is used, all parameters must be specified using named notation.

Any parameter may have a default value. This is accomplished in one of two ways, as shown in this example:

PROCEDURE Calculate_Interest (nAccountNumber IN     number,
                              nInterestRate  IN     number DEFAULT 5.35);
PROCEDURE Calculate_Interest (nAccountNumber IN     number,
                              nInterestRate  IN     number := 5.35);

Stored procedures and functions will receive much more detailed attention in Chapters 4 and 5.


Previous Table of Contents Next