Oracle PL/SQL Best Practices

Name

MOD-15: Validate complex business rules with DML triggers.

Synopsis

Foreign key, NOT NULL, and check constraints provide mechanisms to validate simple business rules like:

An account transaction must be for a valid account.

or:

If the transaction type is DEP the amount must be entered.

However, there are some cases they simply can’t handle. Consider the following requirements:

If the account transaction has been approved, it can’t be updated.

Account transactions can’t be created with approved status.

Regardless of the complexity of the logic behind evaluating the approved status of a transaction, it probably isn’t something a simple constraint can handle. In these cases, database triggers step in with the ability to support arbitrarily complex logic, while simultaneously guaranteeing that applications can’t sidestep the rules.

Example

Here are some examples of simple trigger logic that still can’t be handled with constraints.

If the account transaction has been approved, it can’t be updated:

CREATE TRIGGER cannot_change_approved
BEFORE UPDATE ON account_transaction
FOR EACH ROW
BEGIN
  IF :OLD.approved_yn = constants.yes
  THEN
    err_pkg.raise (account_rules.c_no_change_after_approval);
  END IF;
END;

Account transactions can’t be created with approved status:

CREATE TRIGGER cannot_create_approved
BEFORE INSERT ON account_transaction
FOR EACH ROW
BEGIN
  IF :NEW.approved_yn = 'Y' 
  THEN
    err_pkg.raise (account_rules.c_no_preapproval);
  END IF;
END;

These business rules must be validated in triggers ...