0283-0286

Previous Table of Contents Next

Page 283

NOTE

You can link up to 12 database triggers to a given table.

There are three distinct pieces to consider when building a database trigger. First is the event that causes the trigger to fire. This firing leads to the action to be taken, which you can think of as the database trigger code. Finally, you must consider any optional constraints you might want to place on your trigger. Let's take a closer look at how database triggers are built.

Like packaged procedures, all database triggers follow a standard form in development. The following is an example of creating a database trigger:

 CREATE TRIGGER name   (trigger firing event)   ...   (optional trigger constraint) BEGIN   (trigger action) END; 

As shown by this example, all database triggers start with the CREATE TRIGGER name statement that is the trigger entry point for the named trigger. The trigger firing event code begins with a keyword to specify when the trigger is to fire. This section of code identifies the SQL operation that passes control to the trigger action code. Any constraints on the SQL operation are identified in the optional trigger constraint specification area.

NOTE

If you do not own the table where you are creating the database trigger, you must have the ALTER or ALTER ANY TABLE privilege on the table. Another privilege you need is CREATE TRIGGER, regardless of the table where you create the database trigger.

The following example illustrates the creation and use of a database trigger:

 CREATE TRIGGER check_order_total   AFTER UPDATE OF order_total ON order   FOR EACH ROW   WHEN (new.status = `NEW') BEGIN   IF :new.order_total = 0 THEN     INSERT INTO order_log        values(:new.order);     UPDATE order SET :new.status = `ERR';   END IF; END; 

Page 284

This example shows that the trigger event specification begins with a keyword, AFTER in this case, that determines when the trigger should fire. The FOR EACH ROW statement has the trigger fire once for each row instead of the default of once per table. A constraint on firing is that the status of the updated order must be `NEW'. The trigger action is to INSERT a row into the order_log table and UPDATE the order status to `ERR'.

NOTE

A correlation name such as :new refers to newly updated column values. You can also reference :old values of a changing column. As shown, you do not use the colon in the optional trigger constraint code. You can find more information on database triggers in Chapter 28, "Enforcing Integrity."

CAUTION

You cannot use COMMIT, ROLLBACK, or SAVEPOINT statements in database triggers.

More on Exceptions

I previously discussed using exception handling within PL/SQL, but there are three more areas to consider: reraising exceptions, continuing procedure execution after an exception, and retrying a transaction.

Reraising Exceptions

I discussed the RAISE statement as it is used to raise exceptions within your code, but you can also use RAISE to reraise an exception. Consider the following example:

 CREATE OR REPLACE PACKAGE order_total AS   ot_failure      EXCEPTION;   ...   BEGIN     ...     BEGIN       ...       if g_recip_counter > max_lines then         RAISE ot_failure;       end if;     EXCEPTION       when OT_FAILURE then         ...       RAISE;     END;   ...   EXCEPTION     when OT_FAILURE then 

Page 285

 ...   END; END order_total; 

In this example, the exception is raised in a subblock with an ot_failure exception handler defined. After processing this error inside the handler, the exception is reraised for further processing in the main procedure block. This is accomplished with another ot_failure exception handler.

Continuing Execution

After an exception is raised in a PL/SQL subblock, it is possible to continue execution before exiting the driving block. This is accomplished by placing executable code in the driving block after the exception handler. The following is an illustration of this technique:

 <<outer>> BEGIN   ...   <<inner>>   BEGIN     ...     if g_recip_counter > max_lines then       raise ot_failure;     end if;   EXCEPTION     when OT_FAILURE then       ...   END inner;   UPDATE order SET status = `SU';   INSERT INTO suspense_queue   VALUES (order,g_out_msg); EXCEPTION   ... END outer; 

This example shows that the exception was handled in the inner subblock, at which time control was passed to the outer driving block. As the inner exception handler ended, execution resumed with the UPDATE statement in the outer block. In this manner, execution of the procedure can continue after an otherwise fatal error is encountered .

Retrying Transactions

Another method of continuing procedure execution after an exception is raised is known as retrying a transaction. The technique is similar to continuing execution after a raised exception in that the transaction to be retried must exist in a subblock. Using iterative loop control, you can repeat a transaction as often as you want after an exception is raised. The following example illustrates the use of this technique:

 BEGIN   ...   FOR i in 1..10 LOOP     ...     BEGIN       SAVEPOINT update_order 

Page 286

 (SQL transactions)       COMMIT;       EXIT;     EXCEPTION       WHEN ... THEN         ROLLBACK to update_order            (fix data problems)       ...     END;   END LOOP; END; 

Under the control of the FOR loop, the SQL transactions can be tried a total of 10 times before procedure execution is terminated . The SAVEPOINT update_order is the point of rollback for failed transactions. If an error is encountered during the SQL transactions phase of this subblock, control transfers to the exception handler, which tries to resolve the data problems. After execution of the error handler, control transfers to the FOR loop for another pass.

More on Control Structures

Two additional topics for discussion on the subject of PL/SQL control structures are the EXIT statement and sequential control statements. Both control structures are rarely used, but you might encounter a situation that requires the functionality.

The EXIT Statement

Earlier in the chapter, I mentioned the use of the EXIT statement as a means to break out of a basic FOR loop. You can also use the EXIT statement in retrying transactions after raised exceptions. In this respect, EXIT provides a mechanism for unconditional transfer of control from one point of code to another and must be used selectively, if at all.

Sequential Control

Using sequential control in PL/SQL is not an essential element in successful code development. However, the technique is worth mentioning for a comprehensive view of the language. Two statements are available for sequential control: GOTO and NULL.

GOTO is an unconditional branch statement that transfers control to a label defined within the scope of the branch logic. The label must precede an executable statement or define a PL/SQL block, as shown in the following example:

 <<count_lines>> for i in 1..g_line_counter LOOP   ...   if i>max_lines then     GOTO clean_up;   end if;   ... end LOOP init_lines; <<clean_up>> g_recip_counter = i-1; ... 

Previous Table of Contents Next