Oracle PL/SQL Best Practices

Name

MOD-17: Use operational directives to provide more meaningful error messages from within triggers.

Synopsis

You can create a single trigger that fires for more than one DML operation, as in:

CREATE OR REPLACE TRIGGER check_for_reserved_status
   BEFORE UPDATE OR INSERT ON book
FOR EACH ROW

This allows you to consolidate logic that must be applied to all these operations into a single program unit. If you do this, however, you should take advantage of built-in functions defined in the default DBMS_STANDARD package to help you determine exactly which type of operation was executed.

Here are the headers of those special functions or “operational directives”:

FUNCTION INSERTING RETURN BOOLEAN;
FUNCTION DELETING  RETURN BOOLEAN;
FUNCTION UPDATING  RETURN BOOLEAN;
FUNCTION UPDATING (COLNAM VARCHAR2) RETURN BOOLEAN;

Example

This example allows a single trigger to ensure that approved transactions are neither changed nor deleted, while displaying an informative message when they are:

CREATE OR REPLACE TRIGGER check_approved
   BEFORE UPDATE OR DELETE
   ON account_transaction
   FOR EACH ROW
BEGIN
   IF :old.approved_yn = 'Y'
   THEN
      IF updating
      THEN
         err_pkg.raise (
            te_account_transaction.c_no_update_after_approved);
      ELSE
         err_pkg.raise (
            te_account_transaction.c_no_delete_after_approved);
      END IF;
   END IF;
END;

Benefits

Single triggers can provide meaningful messages.