7.3 Statement Level Aggregation

7.3 Statement Level Aggregation

Statement triggers can enforce business rules where the rule is based on a table aggregate. Although a row trigger can restrain a salary for a particular row, a statement trigger can constrain the result of SUM(salary). This is essentially a constraint on the entire table.

This is quite different from constraints discussed so far. Initially, simple rules are enforced with a CHECK constraint. Chapter 6 illustrates a more complex rule that restricts the salary for any professor in the English department while leaving the CHECK constraint to enforce a salary restriction for non-English professors.

Consider the following rules.

A professor cannot be added if the current budget exceeds $55,000.

The total budget cannot exceed $60,000.

These can be enforced with statement level triggers. The first trigger, BEFORE STATEMENT, will reject any transaction that attempts to add a professor when the current budget exceeds $55,000. The AFTER STATEMENT trigger rejects the transaction when the result of adding a professor exceeds the sum of $60,000. The data for the PROFESSORS table in Chapter 4, Section 4.4, "Sample Data," shows the current sum at $50,000. We could add a single professor with a salary of $10,000. This would pass the business rule test. However, the BEFORE STATEMENT trigger will reject any additional inserts .

Secondly, we could insert a salary of $5,000. This would be allowed. However, the AFTER STATEMENT trigger will likely reject any sizable insert after that.

The implementation of these business rules will require the following steps.

• Update the ERRORS package with error numbers .

• Encapsulate the business rule logic in a constraints enforcement package.

• Write the before and after statement trigger.

The first step is to declare error numbers and message text in the errors package from Chapter 6. This is the central definition for errors raised through the application. The ERRORS package is updated to include two error numbers: 20002 and 20003.

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) :=
    'No additions if the budget exceeds ,000.00';

    app_error_03_txt CONSTANT VARCHAR2(100) :=
    'Budget cannot be over ,000.00';
END errors;

The next step is to encapsulate the business logic in a constraints package. Such a package was developed in Chapter 6. We can revive PROFESSORS_CONS. The following shows the package spec and body, minus the package procedure code from Chapter 6. The following code implements a single procedure that is used by the BEFORE and AFTER statement trigger. There are several styles for coding this. One is to make a separate procedure for each trigger. With this interface the trigger must pass the arguments that specify the constraint limit, error code, and text.

CREATE OR REPLACE PACKAGE professors_cons IS
    PROCEDURE constrain_budget
        (limit NUMBER,err_code PLS_INTEGER,err_text
        VARCHAR2);
END professors_cons;

CREATE OR REPLACE PACKAGE BODY professors_cons IS
    PROCEDURE constrain_budget
        (limit NUMBER,err_code PLS_INTEGER,err_text
        VARCHAR2)
    IS
        budget_sum NUMBER;
    BEGIN
        SELECT SUM(salary) INTO budget_sum FROM
        professors;
        IF budget_sum > limit THEN
            RAISE_APPLICATION_ERROR(err_code, err_text);
        END IF;
    END constrain_budget;
END professors_cons;

The BEFORE and AFTER statement triggers are the last and final step. As always, the body of the trigger is short and simple. Because both INSERT and UPDATE statements can potentially violate the rule, the triggering event is set to INSERT OR UPDATE.

CREATE OR REPLACE TRIGGER professors_bis
BEFORE INSERT OR UPDATE ON professors
BEGIN
    professors_cons.constrain_budget
        (55000, errors.budget_err_1,
        errors.budget_err_1_txt);
END;

CREATE OR REPLACE TRIGGER professors_ais
AFTER INSERT OR UPDATE ON professors
BEGIN
    professors_cons.constrain_budget
        (60000, errors.budget_err_2,
        errors.budget_err_2_txt);
END;

If these rules are in place (i.e., the packages and trigger compile) we can insert this next professor:

INSERT INTO professors VALUES
    ('Smith', 'Mathematics', SYSDATE,
      10000.00, 'YES','MATH');

However, any further INSERTs will be rejected by the BEFORE statement trigger. This is due to the fact that the current SUM(SALARY) exceeds $55,000.

We can insert this professor:

INSERT INTO professors VALUES
    ('Smith', 'Mathematics', SYSDATE,
      5000.00, 'YES','MATH');

However, any sizable addition to the staff will pass the first validation in the BEFORE statement trigger (the budget is at $55,000), but will be rejected by the second, the AFTER statement trigger. This would occur if the salary of this addition caused the SUM(SALARY) to exceed $60,000.