Unfortunately, there is something of a problem with the trigger I just defined. I defined the trigger as an autonomous transaction because I performed the alert in the body of the trigger. But what if I want to perform some additional DML for the main transaction here in the trigger? It won't be rolled back with the rest of the transaction if a rollback occurs. This is unacceptable from the perspective of data integrity.
Generally, I would recommend that you not make a database trigger itself the autonomous transaction. Instead, push all of the independent DML activity (such as writing to the audit or history table) into its own procedure, make that procedure the autonomous transaction, and have the trigger call the procedure.
The autontrigger2.sql script available on the O'Reilly site contains the following reworking of the database trigger. First, I create the audit procedure:
/* File on web: autontrigger2.sql */CREATE OR REPLACE PROCEDURE audit_bonus_comp (name IN VARCHAR2, description IN VARCHAR2, occurred_on IN DATE)IS PRAGMA AUTONOMOUS_TRANSACTION;BEGIN INSERT INTO bonus_comp_history VALUES (audit_bonus_comp.name, audit_bonus_comp.description, audit_bonus_comp.occurred_on); COMMIT;END;Then I change the trigger to the following.
CREATE OR REPLACE TRIGGER aft_ins_bonus_compAFTER INSERT ON bonus_compensation FOR EACH ROWDECLARE ok BOOLEAN:= is_valid_comp_info (:NEW.name);BEGIN IF ok THEN audit_bonus_comp (:new.name, 'AFTER INSERT', SYSDATE); ELSE RAISE VALUE_ERROR; END IF;END;Note the following differences:
The trigger is now an AFTER INSERT trigger rather than a BEFORE INSERT trigger. I want to wait until after the INSERT to the compensation table takes place. Then I will perform my audit.
When the is_valid_comp_info function returns FALSE, I will not even perform an audit. Instead, I will stop the transaction by raising an error. This demonstrates the other reason that the trigger itself should not be autonomous. In some situations, I always want to perform my audit, but under other circumstances, I may want to stop my main transaction by raising an exception. I can't have both of those events happen if the exception is raised in the same block and transaction as the audit DML.
As you take advantage of the new autonomous transaction pragma, plan how you will be using these new code elements. You will almost always be better off hiding the details of your new, independent transactions behind a procedural interface.
Chapter 14. Data Retrieval
One of the hallmarks of the PL/SQL language is its tight integration with the Oracle database, both for changing data in database tables and for extracting information from those tables. This chapter explores the many features available in PL/SQL to query data from the database and make that data available within PL/SQL programs.
When you execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement and also manages the data specified by the SQL statement in the System Global Area (SGA). The private work area contains information about the SQL statement and the set of data returned or affected by that statement.
|
|
|
PL/SQL provides a number of different ways to name this work area and manipulate the information within it; all of these ways involve defining and working with cursors. They include:
Implicit cursors
A simple and direct SELECT... INTO retrieves a single row of data into local program variables. It's the easiest (and often the most efficient) path to your data, but can often lead to coding the same or similar SELECTs in multiple places in your code.
Explicit cursors
You can declare the query explicitly in your declaration section (local block or package). In this way, you can open and fetch from the cursor in one or more programs, with a granularity of control not available with implicit cursors.
Cursor variables
Offering an additional level of flexibility, cursor variables (declared from a REF CURSOR type) allow you to pass a pointer to a query's underlying result set from one program to another. Any program with access to that variable can open, fetch from, or close the cursor.
Cursor expressions
New to Oracle9i, the CURSOR expression transforms a SELECT statement into a REF CURSOR result set and can be used in conjunction with table functions to improve the performance of applications.
Dynamic SQL queries
Oracle allows you to construct and execute queries dynamically at runtime using either native dynamic SQL (NDS) (new to Oracle8i and covered in Chapter 15) or DBMS_SQL Details on this built-in package are available in Oracle Built-in Packages (O'Reilly).
This chapter explores implicit cursors, explicit cursors, cursor variables, and cursor expressions in detail.
