6  select avg(price)
  7  into Running_Avg
  8  from Block_Trade_Log
  9  where
 10  Stock_Symbol = Stock_Symb and
 11  Timestamp >= SYSDATE-3;
 12
 12  return Running_Avg;
 13
 13  end;
 14  /
Function created.

Listing 11.37 contains a modified version of Block_Trade_Log_BI that calls the stored function Get_3_Day_Running_Avg.

Listing 11.37. Modifying the stored function to be called by a trigger.

SQL> create or replace trigger Block_Trade_Log_BI before
  2  insert on Block_Trade_Log
  3  for each row
  4
  4  declare
  5
  5  Running_Avg  number;
  6
  6  begin
  7
  7  :new.Running_Avg_3_Days :=
           ÂGet_3_Day_Running_Avg (:new.Stock_Symbol);
  8
  8  end;
  9  /
Trigger created.

Dropping, Enabling, and Disabling
Triggers

If you've decided that you absolutely don't want a particular trigger, you can drop it with the following statement:

DROP TRIGGER trigger-name;

The variable trigger-name is the name of the trigger to be dropped.

For example, to drop the DELETE AFTER trigger on the Repair Header table, issue the following statement via SQL*Plus:

SQL> drop trigger Repair_Header_Delete_After;
Trigger dropped.

Sometimes, dropping a trigger is too drastic. Instead, you might want to deactivate a trigger temporarily. You can disable a trigger until enabling it again makes sense. To disable a trigger temporarily, use the ALTER TRIGGER statement

ALTER TRIGGER trigger-name DISABLE;

The variable trigger-name is the trigger to disable.

The following example disables the trigger Repair_Header_Delete_After:

SQL> alter trigger Repair_Header_Delete_After disable;
Trigger altered.

To enable a disabled trigger, use the statement

ALTER TRIGGER trigger-name ENABLE;

The variable trigger-name is the trigger to enable.

For instance, you can enable Repair_Header_Delete_After by issuing the following command:

SQL> alter trigger Repair_Header_Delete_After enable;
Trigger altered.

Summary

Congratulations! You've covered a lot of material in this lesson. The most important concepts in this lesson were as follows:

• In PL/SQL an error or warning is called an exception.
• A number of pre-defined exceptions are associated with Oracle SQL errors.
• An exception handler is a series of PL/SQL statements that are executed in response to a raised exception.
• You may declare application-specific exceptions and specify exception handlers for them.
• A PL/SQL block may have an exception section that contains one or more exception handlers.
• You use the RAISE statement to raise a user-defined exception.
• Oracle provides a procedure named RAISE_APPLICATION_ERROR to return an
• application-specific error to the caller of a PL/SQL subprogram.
• You must use a cursor if a query might return more than one row.

• A cursor is a window into the set of rows retrieved by a query.
• You must declare every explicit cursor in the declaration section of a PL/SQL subprogram.
• You can declare a cursor with parameters that have optional default values. The value of each parameter may be specified when the cursor is opened.
• The query associated with a cursor is executed when the cursor is opened.
• You obtain the results of a query by fetching rows from a cursor.
• Every cursor has four attributes: %NOTFOUND, %FOUND, %ISOPEN, and %ROWCOUNT.
• A PL/SQL subprogram can have multiple cursors open simultaneously.
• A database trigger is a PL/SQL block that executes when a triggering event—INSERT, UPDATE, or DELETE—occurs.
• You define a trigger with the CREATE TRIGGER statement, which can be issued from SQL*Plus.
• You can use a trigger for a variety of purposes that include enforcing a security policy, performing complex validation of column values, and calculating the default column values.
• A row-level trigger fires for each row that is affected by a trigger. You can inspect or modify a row's column values with a row-level trigger.
• A statement-level trigger is fired only once by the triggering event. You cannot use a statement-level trigger to access the column values of any row.
• A BEFORE trigger fires before its triggering event. An AFTER trigger fires after its triggering event.
• A trigger may have an optional clause that specifies a condition that must be true for the trigger to fire.
• You can drop a trigger with the DROP TRIGGER statement.
• You can enable or disable a trigger by using the ALTER TRIGGER statement.
• A trigger body cannot contain a COMMIT or ROLLBACK statement.
• You can invoke a stored procedure or function from within the trigger body.

What Comes Next?

Now that you're familiar with SQL and PL/SQL, it's time to move on to something completely different. On Day 12, "Developer/2000: Introduction to Oracle Forms," you are introduced to the tool that you'll use to build a client/server application.

Q&A

Q Is it possible to create a trigger that will fire if a row is read during a query?

A No, a trigger will only fire for an INSERT, UPDATE, or DELETE. You can use the auditing capability in the Oracle RDBMS to record each query submitted by
a user.

Q What is the advantage to using a trigger to check for a condition that is already enforced by referential integrity?

A Some entity-relationship design tools generate triggers that seem superfluous. That is, the generated triggers check for violations of referential integrity and return specific error messages. Without such a trigger, the Oracle RDBMS would return an error message indicating that referential integrity was violated but would not specifically identify the offending columns or relationships. These generated triggers make it easy for both developer and user to determine why the operation was disallowed.

Q Is it always necessary to close a cursor?

A It is if you want to change a value of a variable that is used by the cursor and reopen it. In general, it's good practice to close a cursor when it's no longer needed.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercises before you continue with tomorrow's lesson.

Quiz

1. How are SQLCODE and SQLERRM used in PL/SQL subprograms?
2. True or false? A PL/SQL subprogram may have multiple cursors open at the same time.
3. What are the benefits of using database triggers?
4. What kind of database trigger would you use if you wanted to modify the value to be stored in a column when a new row is added to a table?

Exercises

1. Create a stored function, Teaching_Load, that has a single argument—a Department ID. Teaching_Load should return the average number of courses that instructors in the specified department are currently teaching.