6.6 A Table with Oracle Constraints and Business Rules

In this section a set of business rules is applied to INSERTS on the PROFESSORS table. We will initially enforce the rules with a BEFORE INSERT row trigger. This trigger will perform a variety of tasks . It will:

• Override column values upon insert.

• Enforce a strict business rule that may potentially result in the transaction being rejected through a call to RAISE_APPLICATION_ERROR.

• Take an action. In this case print using DBMS_OUTPUT.

6.6.1 The Environment

The location of this logic is illustrated in Figure 6-2. The grayed area indicates where this trigger code fits into the overall INSERT statement process.

Figure 6-2. Location of Trigger Code.

The business rules in this example are being applied to the PROFESSORS table. Here is a description of that table:

 SQL> desc professors  Name                           Null?    Type  ------------------------------ -------- ------------  PROF_NAME                      NOT NULL VARCHAR2(10)  SPECIALTY                      NOT NULL VARCHAR2(20)  HIRE_DATE                      NOT NULL DATE  SALARY                         NOT NULL NUMBER(7,2)  TENURE                         NOT NULL VARCHAR2(3)  DEPARTMENT                     NOT NULL VARCHAR2(10) 

and the CHECK constraints for this table are:

• The name of the department is restricted to a limited set.

   ALTER TABLE professors     ADD CONSTRAINT ck_professors_department     CHECK (department IN ('MATH','HIST','ENGL','SCIE')); 

• The tenure field is YES or NO.

   ALTER TABLE professors     ADD CONSTRAINT ck_professors_tenure     CHECK (tenure IN ('YES','NO')); 

• The salary has an upper limit of 30,000.00.

   ALTER TABLE professors     ADD CONSTRAINT ck_professors_salary     CHECK (salary < 30000); 

6.6.2 The Procedural Constraints to Enforce

The following lists the rules we are asked to enforce. Rules are grouped using one the categories from in Figure 6-1 and 6-2.

OVERRIDE COLUMN RULES

• Truncate the HIRE_DATE to ensure that all inserted dates have a zero for hours, minutes, and seconds.

• Convert the DEPARTMENT to uppercase. This is just to ensure that an INSERT of 'Math' is converted to 'MATH.'

• Round all new salaries to the nearest dollar.

REJECT THE TRANSACTION RULE

• Reject any INSERT where the salary of the professor exceeds a $10,000 limit and the department is 'ENGL.' Note: All other salaries are constrained to the CHECK constraint that has a limit of $30,000.

TAKE ACTION RULE

• Write a message using DBMS_OUTPUT.

The trigger code first converts the :NEW values. This includes truncating the HIRE_DATE column. Following that, it checks for English department salary restriction. This can potentially reject the insert. Finally, the trigger takes the action to print.

 CREATE OR REPLACE TRIGGER professors_bir BEFORE INSERT ON professors FOR EACH ROW DECLARE     msg VARCHAR2(100) :=         'The salary exceeds the ENGL maximum of ,000.00'; BEGIN     -- --------------------------------------     -- OVERRIDE COLUMN RULES     -- --------------------------------------     -- truncate hours, minutes, seconds of hire date     :NEW.hire_date := TRUNC(:NEW.hire_date);     -- round salary     :NEW.salary := ROUND(:NEW.salary);     -- convert department to upper case     :NEW.department := UPPER(:NEW.department);     -- --------------------------------------     -- REJECT TRANSACTION RULES     -- --------------------------------------     IF :NEW.department='ENGL' AND :NEW.salary > 10000 THEN         RAISE_APPLICATION_ERROR(-20000,msg);     END IF;     -- --------------------------------------     -- TAKE ACTION RULES     -- --------------------------------------     dbms_output.put_line        ('Before Insert Row Trigger Action'); END; 

6.6.3 Before versus After

The aforementioned trigger enforces several business rules. All are enforced prior to Oracle's constraint enforcement. The "Override Column" trigger code must be in a Before-Insert Row trigger, but the "Reject the Transaction" and "Take Action" logic should be after the Oracle constraint. In general, you only want to process data for an application business rule that you know is good ”data that has passed integrity checks. This shifts the location of the code to the architecture drawn in Figure 6-3.

Figure 6-3. Improved Location of Trigger Code.

To implement this change we need to create two triggers and split the PL/SQL logic between the triggers. The BEFORE-INSERT trigger performs the "Override Column" function. The remaining trigger code goes in an AFTER-INSERT row trigger. The following trigger is the BEFORE-INSERT row trigger, with comments removed.

 CREATE OR REPLACE TRIGGER professors_bir BEFORE INSERT ON professors FOR EACH ROW BEGIN     :NEW.hire_date := TRUNC(:NEW.hire_date);     :NEW.salary := ROUND(:NEW.salary);     :NEW.department := UPPER(:NEW.department); END; 

The AFTER-INSERT trigger, which runs after Oracle's constraint enforcement, will perform the "Reject the Transaction" task (check for an invalid salary condition) and perform the "Take Action" task, which is to print a message. The following is the AFTER-INSERT trigger, comments removed.

 CREATE OR REPLACE TRIGGER professors_air AFTER INSERT ON professors FOR EACH ROW DECLARE     msg VARCHAR2(100) :=         'The salary exceeds the ENGL maximum of' ,000.00'; BEGIN     IF :NEW.department='ENGL' AND :NEW.salary > 10000 THEN         RAISE_APPLICATION_ERROR(-20000,msg);     END IF;     dbms_output.put_line        ('Before Insert Row Trigger Action'); END; 

6.6.4 Using Packages for Procedural Constraints

Rules for a trigger can pile up. A trigger can quickly become complex. Because an invalid trigger can interfere with the operations of an application, logic should be moved from the trigger to PL/SQL packages. If a piece of code is not performing as expected, that procedure call can be removed from the trigger. A trigger can and should be able to enforce many business rules, but each rule should be autonomous.

Each check constraint can be enabled and disabled with an ALTER TABLE statement. You should be able to accomplish the same level of granularity with triggers.

The following illustrates encapsulating the business rule logic in a package. This package is called PROFESSORS_CONS because it enforces constraints on the PROFESSORS table. The package specification is listed first and then the triggers.

 CREATE OR REPLACE PACKAGE professors_cons IS     FUNCTION salary(sal professors.salary%TYPE)         RETURN professors.salary%TYPE;     FUNCTION hire_date(hd professors.hire_date%TYPE)         RETURN professors.hire_date%TYPE;     FUNCTION department(dept professors.department%TYPE)         RETURN professors.department%TYPE;     PROCEDURE validate_salary         (dept professors.department%TYPE,          sal professors.salary%TYPE);     PROCEDURE print_action; END professors_cons; 

The triggers only make calls to individual functions in the package. The BEFORE-INSERT trigger uses the logic in the package rather than perform each individual truncate and round. Granted this is not much logic to encapsulate. ROUND, UPPER, and TRUNC are not extensive data transformations. However, a more realistic business rule could stipulate that the HIRE_DATE on an insert be changed to the next business day. Such a transformation could include database lookups to determine the next working day.

The BEFORE-INSERT row trigger with calls to the constraints package is shown here.

 CREATE OR REPLACE TRIGGER professors_bir BEFORE INSERT ON professors FOR EACH ROW BEGIN     :NEW.hire_date :=             professors_cons.hire_date(:NEW.hire_date);     :NEW.salary :=             professors_cons.salary(:NEW.salary);     :NEW.department :=             professors_cons.department(:NEW.department); END; 

The AFTER-INSERT trigger is two procedure calls.

 CREATE OR REPLACE TRIGGER professors_air AFTER INSERT ON professors FOR EACH ROW BEGIN     professors_cons.validate_salary             (:NEW.department, :NEW.salary);     professors_cons.print_action; END; 

The package body that implements the business rules is the following.

 CREATE OR REPLACE PACKAGE BODY professors_cons IS     FUNCTION salary(sal professors.salary%TYPE)         RETURN professors.salary%TYPE IS     BEGIN         RETURN ROUND(sal);     END salary;     FUNCTION hire_date(hd professors.hire_date%TYPE)         RETURN professors.hire_date%TYPE IS     BEGIN         RETURN TRUNC(hd);     END hire_date;     FUNCTION department(dept professors.department%TYPE)         RETURN professors.department%TYPE IS     BEGIN         RETURN UPPER(dept);     END department;     PROCEDURE validate_salary         (dept professors.department%TYPE,          sal professors.salary%TYPE)     IS         msg VARCHAR2(100) :=          'The salary exceeds the ENGL maximum of ,000.00';     BEGIN         IF dept ='ENGL' AND sal  > 10000 THEN             RAISE_APPLICATION_ERROR(-20000,msg);         END IF;     END validate_salary;     PROCEDURE print_action IS     BEGIN         dbms_output.put_line             ('Before Insert Row Trigger Action');     END print_action; END professors_cons; 

6.6.5 Managing Error Codes and Messages

Large systems (e.g., operating systems and database products) do not embed error code numbers and messages as literal values within the code. The following procedure, taken from the professor's constraint enforcement package in Section 6.5, is an example of a buried error code number and message text.

 PROCEDURE validate_salary         (dept professors.department%TYPE,          sal professors.salary%TYPE)     IS         msg VARCHAR2(100) :=          'The salary exceeds the ENGL maximum of' ',000.00';     BEGIN         IF dept ='ENGL' AND sal  > 10000 THEN             RAISE_APPLICATION_ERROR(-20000,msg);         END IF;     END validate_salary; 

As new code is developed, the management of error codes is important. An application developer needs to know the next available message number. This can easily be determined when error code numbers are centralized. The following package is an example of encapsulating error messages.

The following errors package encapsulates the error numbers and message text for all the errors of an application.

 CREATE OR REPLACE PACKAGE errors IS     eng_dept_sal CONSTANT PLS_INTEGER := -20001;     app_error_02 CONSTANT PLS_INTEGER := -20002;     app_error_03 CONSTANT PLS_INTEGER := -20003;     eng_dept_sal_txt CONSTANT VARCHAR2(100) :=         'The salary exceeds the ENGL maximum of' ',000.00';     app_error_02_txt CONSTANT VARCHAR2(100) := 'vacant';     app_error_03_txt CONSTANT VARCHAR2(100) := 'vacant'; END errors; 

This changes the procedure in the constraints enforcement package for the professor's table to the following:

 PROCEDURE validate_salary         (dept professors.department%TYPE,          sal professors.salary%TYPE)     IS     BEGIN         IF dept ='ENGL' AND sal  > 10000 THEN             RAISE_APPLICATION_ERROR                 (errors.eng_dept_sal,                  errors.eng_dept_sal_txt);         END IF;     END validate_salary; 

6.6.6 Trigger Architecture

We started with a set of business rules that were enforced within a single row trigger. Logically, it made sense to split the logic between the BEFORE-INSERT and AFTER-INSERT triggers. The specific logic of each rule was encapsulated into a package. The error definitions were further encapsulated into an errors package. The final architecture is shown in Figure 6-4.

Figure 6-4. Final Architecture.

The ERRORS package has no body. It is a central repository for constants that can be referenced by various parts of the business rule enforcement application code.