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
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
Consider the following rules.
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
Secondly, we could insert a salary of $5,000. This would be allowed. However, the AFTER STATEMENT trigger will likely reject any
The implementation of these business rules will require the following steps.
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;
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
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.