0706-0708

Previous Table of Contents Next

Page 706

ORDER ensures that sequences are used in the order generated. Regardless of the setting of this option, the same value will never be returned twice. If an application uses a sequence in a transaction that is rolled back, the value simply is discarded. NORDER, the default for this option, is acceptable unless the sequence is being used as a timestamp to indicate the order of events over time.

A sequence has two pseudocolumns : currval and nextval. currval returns the current value of the sequence, whereas nextval increments the sequence and returns the new value. Listing 28.15 demonstrates how a sequence can be used in an Oracle function to generate new keys when inserting records.

Listing 28.15. This function accesses a sequence to fetch a new key when inserting a record.

 CREATE OR REPLACE FUNCTION insert_indiv (last CHAR, first CHAR,                            notes CHAR, dob DATE) RETURN NUMBER IS     new_id NUMBER; BEGIN     SELECT individual_ids.nextval INTO new_id FROM dual;     INSERT INTO individual VALUES (new_id, last, first, notes,                                    dob, user, sysdate);     COMMIT;     RETURN(new_id); EXCEPTION     WHEN OTHERS THEN         ROLLBACK;         RETURN(1); END insert_indiv; 

It is a common practice to use sequences for generating unique primary keys. One sequence can be used for many tables, or a separate sequence can be created for each table requiring generated keys. Either option is preferable to any key that requires user intervention, because typographical errors are bound to occur. It typically is preferable to generate unique keys even when one exists naturally in the data (a Social Security number, for example).

Using a sequence to generate primary keys can improve performance in certain situations as well. As mentioned previously, integer joins typically are faster than character-based joins; even when a natural integer primary key exists, a sequence is often a better choice. To use a Social Security number as an example, 10 bytes must be stored for each key. If a sequence is used, starting with 1, a considerable amount of disk space can be conserved and a much smaller index produced, which results in less I/O. Perhaps a less important consideration is the order in which values are inserted. Depending on how inserts are handled by applications accessing the sequence, the index on a sequence-generated primary key may be created in ascending order naturally, which is somewhat of an optimization in terms of I/O performance. If the sequence is created with the ORDER option and inserts are handled using Oracle subprograms similar to Listing 28.15, I/O performance will always be optimized.

Page 707

Using Triggers

Triggers are stored procedures associated with a specific operation on a specific table. A trigger is fired automatically when the operation with which it is associated is performed on the table with which it is associated. Triggers can perform many of the same tasks as constraints, and in most cases, they can go beyond what constraints can do. A NOT NULL constraint can only ensure that a value is present in a column, for example, but it does nothing to ensure the accuracy of the data. Listing 28.16 provides an example of how you can use triggers to enforce a NOT NULL constraint and ensure the accuracy of the data being inserted.

Listing 28.16. This trigger ensures that the columns last_updt_user and last_updt_date are inserted and updated accurately.

 CREATE OR REPLACE TRIGGER indiv_timestamp BEFORE INSERT OR UPDATE ON individual FOR EACH ROW BEGIN     :new.last_updt_user := user;     :new.last_updt_date := sysdate; END indiv_timestamp; 

The simple trigger in Listing 28.16 ensures that the last_updt_user and last_updt_date are being inserted and updated accurately in the individual table. In effect, the trigger intercepts the actual values being inserted and replaces them with user and sysdate. Using the NOT NULL constraint for these columns no longer is necessary, and the trigger goes far beyond what the constraint could do.

NOTE

The trigger in Listing 28.16 also relieves the application of the burden of supplying values for the last_updt_user and last_updt_date columns when inserting and updating records.

INSERT and UPDATE triggers commonly are used for customized transaction logging or to generate statistical summaries to be accessed by a different group of users than those applying the transactions. A large order-entry system, for example, might use an INSERT trigger to write only the date, the order amount, and the salesperson to a separate table to be used only for management reporting.

The syntax for creating triggers is very similar to the creation of procedures, with a few notable exceptions. The BEFORE or AFTER keyword must follow the name of the trigger, indicating whether the trigger should be fired before or after the operation that causes it is fired.

Page 708

NOTE

Although it might not seem logical to do so, the trigger in Listing 28.16 had to be created with the BEFORE option. Trying to implement the same trigger with the AFTER option produces the following error:

 ORA04091: table SCOTTY.INDIVIDUAL is mutating, trigger/function may not see it 

Because the trigger is being executed by a process that currently is involved in a transaction on the same row, it cannot be created with the AFTER option. Using AFTER would, in effect, invalidate the old correlation of the trigger.

Immediately following the BEFORE or AFTER keyword is the action (or actions) with which the trigger is associated. This can be INSERT, UPDATE, DELETE, or any combination of these separated by OR. The FOR EACH ROW keyword defines the behavior of the trigger when it is fired by statements affecting multiple rows. The default behavior is to fire the trigger only once, regardless of the number of rows affected. A trigger also may include a WHEN clause, which limits the conditions under which it will fire.

The WHEN clause can be used for specialized reporting or to draw attention to a value that may seem to be out of range. An accounts payable system, for example, might use an INSERT trigger to log all payments of greater than $10,000 to a temporary table, which then can be used to generate a report for management's review and approval. This could be an alternative to a CHECK condition, which might be overly restrictive . In most circumstances, it would not be acceptable to reject a valid payment simply because it is unusually high. On the other hand, management might be interested in reviewing or auditing these payments. In this respect, you can use a trigger in a way that is analogous to passively enforcing a CHECK constraint.

Note that in Listing 28.16, the variable new is never declared. This is the default correlation name associated with the new row (which is valid for inserts and updates only). The name old is associated with the old row by default and is valid for updates and deletes only. These default names can be reassigned by using a REFERENCING clause. You should place the REFERENCING clause immediately before the FOR EACH ROW keyword (if it is used), as shown in Listing 28.17.

Listing 28.17. This trigger uses a REFERENCING clause to rename new.

 CREATE OR REPLACE TRIGGER indiv_timestamp BEFORE INSERT OR UPDATE ON individual REFERENCING new AS new_row FOR EACH ROW BEGIN     :new_row.last_updt_user := user;     :new_row.last_updt_date := sysdate; END indiv_timestamp; 

The REFERENCING clause rarely is used, but it is provided to allow the use of new and old as regular program variables .

Previous Table of Contents Next