Listing 11.24. continued

 13    exit when Get_Associate_Profs%rowcount >= 5;
 14    dbms_output.put_line (`Rowcount: ` || Get_Associate_Profs%rowcount);
 15
 15  end loop;
 16
 16  end;
 17  /

Rowcount: 1
Rowcount: 2
Rowcount: 3
Rowcount: 4

PL/SQL procedure successfully completed.

As you can see in Listings 11.23 and 11.24, you can use %ROWCOUNT to keep track of the number of rows that have been fetched from a cursor.

Enforcing Business Rules with
Database Triggers

Using everything that you've learned about SQL and PL/SQL, it's time to explore the world of database triggers. In an information system that uses a non-relational database as its foundation, the business rules of the organization served by the system are implemented in application software. For instance, one business rule might be that if the inventory on hand for a part falls below the stocking level for that part, an order for the required quantity is entered into the system. You would commonly enforce this rule by writing a routine in COBOL or some other programming language that is invoked at an appropriate point in the application. However, this method presents several problems:

• The rule is enforced only if an application program or tool invokes the routine. If some other tool is used to modify records in the database, the rule isn't enforced.
• Obtaining a list of the algorithms that are used to enforce business rules is difficult. You have to rely on documentation (which might not be entirely accurate) or face the drudgery of reviewing application software.

Oracle, along with other modern RDBMSs, provides a mechanism—the database trigger—that eases the task of implementing an organization's business rules. A database trigger is a group of PL/SQL statements that is executed when an SQL statement—a DELETE, UPDATE, or INSERT statement—is applied to a table. You can use a database trigger to perform the following tasks:

• Enforce a sophisticated security policy
• Change a column value based on the value of other columns in the same table or a different table
• Perform complex validation of column values—for instance, you might need to compare a column value to some aggregate column value from a different table
• Document changes to a record by writing modified values to another table

NEW TERM
A database trigger is a group of PL/SQL statements that are executed when the contents of a table are modified in some way—either through an INSERT, UPDATE, or DELETE statement.

This lesson examines the details of creating triggers for various purposes.

Creating a Trigger

You'll want to use a text editor to write your triggers. The Oracle statement CREATE TRIGGER creates (or replaces) a trigger that's fired when a specified event occurs on a table.

The syntax for the CREATE TRIGGER statement is as follows:

CREATE [OR REPLACE] TRIGGER trigger-name {BEFORE | AFTER}
triggering-event ON table-name
[FOR EACH ROW]
[WHEN (condition)]
PL/SQL-block

The variables are defined as follows:

trigger-name is the name of the trigger to create and is subject to Oracle object-naming restrictions.

triggering-event is either INSERT, UPDATE, or DELETE corresponding to the three DML statements.

table-name is the name of the table with which the trigger is associated.

FOR EACH ROW is an optional clause that, when used, causes the trigger to fire for each affected row.

condition is an optional Oracle Boolean condition that, when TRUE, enables the trigger to fire.

PL/SQL-block is the PL/SQL block that is executed when the trigger fires—referred to as the trigger body.

The following sections discuss the use of these CREATE TRIGGER statement elements.

Statement-Level and Row-Level Triggers

A database trigger fits in one of the following two classifications:

• Statement-level triggers don't include the clause FOR EACH ROW in the CREATE
• TRIGGER statement.
• Row-level triggers do include the clause FOR EACH ROW in the CREATE TRIGGER statement.

A statement-level trigger fires only once for the triggering event and doesn't have access to the column values of each row that is affected by the trigger. A row-level trigger fires for each row that is affected by the trigger and can access the original and new column values processed by the SQL statement.

You generally use a statement-level trigger to process information about the SQL statement that caused the trigger to fire—for instance, who executed it and when. You typically use a row-level trigger when you need to know the column values of a row to implement a business rule.

Referencing Column Values in the Trigger Body

Within the trigger body, a row-level trigger can reference the column values of the row that existed when the trigger was fired. These values depend on which SQL statement caused the trigger to fire.

• For an INSERT statement, the values that will be inserted are contained in :new.column-name, where column-name is a column in the table.
• For an UPDATE statement, the original value for a column is contained in :old.column-name; the new value for a column is contained in :new.column-name.
• For a DELETE statement, the column values of the row being deleted are contained in :old.column-name.

Triggering Events

When you create a trigger, you specify what event will cause the trigger to fire. The three possible events are

• The insertion of a new row in the table through an INSERT statement
• The modification of a set of rows (or no rows) via an UPDATE statement
• The deletion of a set of rows (or no rows) with a DELETE statement

In addition, you can combine these triggering events so that a trigger fires whenever a DELETE or INSERT or UPDATE statement is executed, as shown in Listing 11.25.

Listing 11.25. Declaring a trigger that fires on any triggering event.

SQL> create or replace trigger Block_Trade_After_All After
  2  insert or update or delete on Tab1
  3  for each row
  4
  4  declare
  5
  5  begin
  6
  6  insert into Tab11
  7  (col11)
  8  values
  9  (11);
 10
 10  end;
 11  /
Trigger created.

BEFORE and AFTER Triggers

A BEFORE row-level trigger is fired before the triggering event is executed. As a result, you can use a BEFORE row-level trigger to modify a row's column values. An AFTER row-level trigger fires after the triggering event has occurred. You can't modify column values with an AFTER trigger.

Possible Triggers for a Table

Based on all of the permutations you can use in the CREATE TRIGGER statement, a single table can have up to 12 types of triggers:

• Six row-level triggers for BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE, AFTER DELETE, AFTER INSERT, and AFTER UPDATE
• Six statement-level triggers for BEFORE DELETE, BEFORE INSERT, BEFORE UPDATE, AFTER DELETE, AFTER INSERT, and AFTER UPDATE

  TIP

  If you're considering the use of an entity-relationship modeling tool for database design—which you should—you'll find that most of them will automatically generate database triggers based on the primary and foreign key relationships you define. Some tools, such as ERwin from LogicWorks, either create the triggers directly via an Oracle connection or store the triggers in a script file. If you choose the latter method, you can modify the trigger creation script by adding application-specific business rules to any triggers that have been generated.

  Previous | Table of Contents | Next
  Используются технологии uCoz