Server-based Rules Enforcement

Neal A.  Fishman
Neal A. Fishman Enterprise Architect, Equifax Read Author Bio || Read All Articles by Neal A. Fishman

In his latest book "What Not How (The Business Rules Approach to Application Development)," C.J. Date described the three parts of an application in relation to business rules - the presentation aspects, the database aspects, and the application-specific aspects. Much of the book dealt with how business rule technology is in alignment with the original relational vision. He suggested that business rules should be an integral part of the data model. And quite so. Today's commercial relational and object/relational database products support business rule constraints in a variety of ways. Tables are defined with one or more columns (HASA facts). Control domains columns are assigned datatypes. Tables can be related to other tables through the use of referential integrity (this establishes the parent/child relationships). Most object/relational databases are now supporting subtyping (ISA facts). Most of the features just mentioned support expressions of business rules that can be considered basic.

For handling more complex business rules within a database, most commercial RDBMS and ORDBMS vendors have implemented support for database triggers. Depending on the database vendor, business rule logic for triggers may be written in either proprietary or non-proprietary database languages. Oracle's PL/SQL and SQL Server's Transact-SQL are examples of proprietary languages. Non-proprietary languages include C, C++, and Java. Regardless of the language, triggers can provide a reasonable mechanism for building server-enforced business rules. An enterprise with server-based rule enforcement can offer centralized business rule logic, from a central repository of triggers and other stored procedures. In a managed environment (one with change control and configuration management), triggers can be easily managed and modified.

In this article we discuss various aspects of implementing business rules through the use of database triggers.

Server-Based Rule Enforcement

When using triggers for server-based rule enforcement we can address many integrity, data quality, and business function needs:

  • Relational databases use a catalog (repository) for all created objects. Therefore, the database can be mined to find out about the business rules associated with triggers; how they're enforced, what tables they act upon, under what conditions they are invoked, etc.
  • Most databases support interprocess communications. This is the ability to converse with applications that are not natively connected to the database. Triggers can be used to initiate messages. One example would be sending a message to another database if there was an attempt to violate a rule on the local database. A second example would be to use a database trigger to manage propagating or spawning actions from one database to another database or system.
  • Using triggers to enforce rules from a centralized standpoint can help enforce modification activity regardless of the origin of the SQL statement. A trigger that constrains data values, enforces the rule whether the SQL Insert, Update, or Delete came from a web browser (or client GUI), a query tool, an in-house application program, a COTS (commercial-of-the-shelf) application, a database's native SQL environment, a local stored-procedure, a remote stored-procedure, a load utility, etc. The trigger can be used to always enforce the rule.
  • Triggers can also be used as a testing tool for applications. They can be used to help find bugs while an application is still in development instead of after it is in production.

Invocation

Most databases allow the designer to choose from several invocation options. These include Before/After and Statement/Row. These options can be set to act on SQL Insert, Update and Delete statements. SQL Insert, Update and Delete statements are sometimes referred to as DML (Data Manipulation Language) statements. Looking at the chart below, it shows the twelve possible combinations of invocation options and DML statements.

 

When Granularity Statement Type
Before Statement Insert, Update, Delete
After Statement Insert, Update, Delete
Before Row Insert, Update, Delete
After Row Insert, Update, Delete
  • Before/After - identifies when the trigger is to be fired. "Before" the originating SQL has started modifying its rows, or "After" the originating SQL has modified its rows.
  • Statement/Row - identifies the granularity of the fired trigger to operate at the "statement (set)" level or the "row" level. If a table has 10 rows in it, and the following SQL statement is executed: DELETE FROM table. All 10 rows will be deleted. The trigger can be set up to fire once (Statement) or 10 times (Row). Similarly, if an SQL statement is to reduce product prices by 10 percent: UPDATE table SET price = price - (( price * 10 ) / 100), it can have its trigger fired once if declared as a Statement level trigger or for every row if declared as a Row level trigger. Statement level triggers are applicable to SQL Insert statements as well. SQL Insert statements can insert more than one row at a time if it includes a Select clause instead of a Values clause: INSERT INTO table SELECT * FROM other_table.

Taking Action

If a trigger detects a violated business rule, it can be coded to act in a number of ways.

  • A trigger can raise an application error, rolling back the transaction, but permitting the application to trap the application error code. This is an active response and shifts the burden of "reacting" to the violation to the application code.
  • A trigger might override a column value during an SQL Insert or Update statement. This type of trigger is passive in nature; it does not "announce" its action. It may choose to replace an excessive salary increase value with a predefined maximum allowed salary increase value.
  • A trigger may issue additional DML statements. This gives the DML statement "built in intelligence." An example might be to add a purchase order line item when inventories for a certain part fall below a given threshold.
  • A trigger could communicate an event to another host process through various communication protocols.
  • A trigger may simply copy column values affected from the SQL statement into another table - a table that could possibly be used for auditing purposes.

Trigger Design

Two key objectives to keep in mind when designing rule-based trigger code is the completeness of rule enforcement, and maintainability of the trigger architecture.

The design process for trigger code must ensure completeness - identifying all events to which rule enforcement logic must respond. Data integrity could be compromised if completeness is not assured.

In this example, a rule enforced with an insert trigger could also require an update trigger. If we constrain the number of line items in a purchase order with an insert trigger, i.e., no more than ten line items in one order, rule enforcement is incomplete if we do not consider an update trigger. What would happen if a customer changes an order made earlier in the day? Unless the update is also constrained, the number of line items for a single order may be violated.

Completeness is particularly important when a single rule is enforced with triggers from different tables. This occurs when an object is constrained by an attribute of a related object - the constraining object - which is stored in a different table.

If the application has a rule that offers a shipping discount to customers with a preferred shipping status. We could develop a trigger on a purchase order table to enforce the discount - but if the customer falls out of favor and receives a non-preferred shipping status, we must consider a trigger to revoke any future discounts for the customer.

As rules are enforced, often with multiple triggers for a single rule and not always on a single table, we want the final trigger architecture to be maintainable and capable of responding to rule changes. We should be able to quickly identify the code that, for example, enforces in-state shipping discounts, modify it, and know that we are modifying only the logic for that particular rule.

The key to maintainability is to encapsulate highly cohesive procedures and functions into a reusable, testable, and manageable system. One should be able to trace from a rule description to a procedure and then back from a procedure to a rule. This allows for rule-tuning for business changes and efficient management of large numbers of rule-based requirements.

Trigger Design Process

Designing and developing triggers to enforce business rules should follow a reasonable software development life cycle (SDLC) practice. The SDLC should include gathering requirements, performing analysis and design, constructing, testing and implementation. The following are some ideas for developing trigger enforced business rules, which can be incorporated into a methodology.

Precision

It may seem obvious, but the first part of trigger design is to express the business requirement or rule with a degree of precision that allows development of a comprehensive trigger algorithm. The precision of the expression can often be validated by asking: "Can I test this rule - can I evaluate it to be TRUE?" and then by recasting the requirement to search for implicit exceptions which may have gone undetected.

 

In this example, "A customer's purchase order cannot exceed $999.99 if the purchase date is a weekend." This is tested by attempting to Insert a purchase order in excess of $999.99 with a purchase date of Saturday or Sunday. If the Insert fails, the requirement has been satisfied, but only for the insert operation. We must also consider operations to update the purchase order; does the same business rule apply to purchase orders made on a weekday and later updated on a weekend, which then exceed the $999.99 limit?

The Steward of the business rule might assume that "weekend" includes holidays and not just Saturday and Sunday; or that "weekend" means 5:00 p.m. Friday to 8:00 a.m. Monday. It is necessary that all terms be defined.

In this example we can more precisely express the business rule as follows:

 

BR#1. A purchase order that originates on a Saturday or Sunday cannot have a purchase amount that exceeds 999.99 US dollars.

BR#1 is shorthand for Business Rule Number 1.

Develop a Rule Violation List

Next identify all the conditions that can potentially violate the business rule. The end of this process results in a list of database Insert, Update, or Delete actions called a Rule Violation (RV) list.

This list can be built by first identifying all of the SQL statements which can violate the business rule. Each violation event contains one of the SQL keywords: Insert, Update, or Delete. It also identifies a table (term) such as a purchase order, customer, or employee.

Each violation is expressed in a form: "what we can do to violate the business rule." BR#1 from above has three conditions that may potentially violate the constraint:

 

RV#1. Insert a purchase order with a purchase date of Saturday or Sunday and a purchase amount that exceeds 999.99 US dollars.

RV#2. Update a purchase order to exceed 999.99 US dollars where the original purchase date is a Saturday or Sunday.

RV#3. Update the purchase date for an existing order. This original order is in excess of 999.99 US dollars and has an original weekday purchase date. The update changes the purchase date to a Saturday or Sunday.

The RV list often results in multiple DML statements for any single rule. For any single rule there is typically an Insert and an Update, or an Update and a Delete. It's not surprising that we have two possible update events that can violate the rule; since a purchase order is constrained by the purchase amount and the purchase date, we can induce a violation by updating either of these columns.

We can analyze the purchase order rule in terms of the constrained and constraining object. The purchase order object, uniquely identified by the primary key, is constrained by the purchase date and purchase amount. The date and amount are the constraining attributes.

When the constraining attributes are in the same table as the constrained entity, the trigger enforcing the rule is defined only on that table. When the constrained and constraining entities are in different tables, the RV list references more than one table - leading to triggers on different tables. There can be other variations:

  • A trigger on the constrained table must read information from a second table for the constraint check - see example RV#4 below.
  • A trigger must be defined on another table and fires to enforce a constraint defined on the constrained entity - see example RV#5 below.

 

The following illustrates an RV list when the constrained and constraining entities are not in the same table.

Assume that a purchase order table has a column for a customer account number (CUSTOMER_ID). Each purchase order detail record (child) references one customer (parent) row. CUSTOMER_ID is a foreign key to the customer table.

CREATE TABLE customer       (customer_id             NUMBER      PRIMARY KEY,
                             shipping_status_code    CHAR(2)     NOT NULL ...

CREATE TABLE purchase_order (order_id NUMBER PRIMARY KEY, customer_id NUMBER REFERENCES customer, purchase_amount NUMBER NOT NULL, purchase_date DATE NOT NULL ...

The following business rule illustrates a constraint, which involves two tables: purchase order and customer. The purchase order is constrained not just by attributes of the purchase order table (purchase date and purchase amount) but also by attributes of the customer who places the order. In this case we use the customer's shipping status code as a constraining attribute (note BR#1 has been extended to include an additional condition.

BR#1. A purchase order that originates on a Saturday or Sunday cannot have a purchase amount that exceeds 999.99 US dollars - this rule applies only to customers that do not have a preferred shipping status.

The RV list for this rule requires insert and update (of purchase date and purchase amount) constraint checks similar to RV#1, 2 and 3 above, with the added proviso that the customer does not have a preferred shipping status. RV#1, 2 and 3 are designed to constrain the purchase based upon information, created or modified, in the purchase order table.

Additionally, the following two RV list entries define possible actions that may force a constraint violation caused by either a relationship (foreign key) change or a value change in another table, i.e., customer. The first condition is the reassigning of a child purchase order to a different customer. The second involves changing the attribute of a customer who owns a purchase order. (There are special considerations when writing code to enforce rules that cross database tables; these topics will be visited following the basic steps outlined in the sections to come.)

RV# 4. Update purchase order and replace the customer account number, CUSTOMER_ID, such that: the old customer has a preferred shipping status, and the new customer does not have a preferred shipping status, and the old customer has a purchase order that was placed on a Saturday or Sunday in excess of 999.99 US dollars.

RV# 5. Update the customer shipping status, such that: the old shipping status was changed from preferred to non-preferred and the customer currently has an outstanding purchase order that exceeds 999.99 US dollars, and the purchase date was made on Saturday or Sunday.

Filter the Rule Violation List

The RV list is a complete list of what can be done by issuing DML statements from any valid environment. The filtering process is an opportunity to review the possible violation conditions within the context of the application code, application design, coding standards, and security.

The following considerations may cause the removal of conditions from the RV list: the use of CHECK or NOT NULL constraints, database table changes, programming standards, and GUI designs that prevent field updates.

We want to avoid writing triggers that are designed to detect a violation from the RV list and be unaware that the event, by design, can never occur. Filtering the RV list is necessary to keep from writing triggers that may always fire but never detect a rule violation.

Consider RV#4 from the example above. This event could only take place if we reassign the purchase order of a preferred shipping status customer to another customer not in a preferred shipping status (this is essentially an update of a foreign key to a child foreign key to a different parent):

 

Update purchase order and replace the customer account number, CUSTOMER_ID, such that: the old customer has a preferred shipping status, and the new customer does not have a preferred shipping status, and the old customer has a purchase order that was placed on a Saturday or Sunday in excess of 999.99 US dollars.

In a GUI environment, if the client window is designed so the user cannot update the customer field of a purchase order window, then by design this event will never occur - unless there is concern about similar updates from server code or ad-hoc environments. We may decide that the front-end design is adequate enforcement and therefore remove RV#4 from the list.

Does this situation split rule enforcement between the server and the client? It does, but we should take advantage of the Forms paradigm - to write-protect a windows field requires no written program code. However, if this protection is insufficient, the rule can be reinforced on the server.

On the other hand, the client-based enforcement of RV#1, 2, and 3 requires code. That we choose, initially, to enforce on the server.

Trigger Identification and Top-Level Design

The next step involves identifying trigger types, and defining high level functional descriptions. We are concerned with identifying from the RV list the table names, actions (i.e., Insert, Update, and Delete), and top-level function descriptions or algorithmic expressions.

To do this, we extract the components from the RV list text. This produces the data illustrated in Tables 1 and 2. Although the RV list identifies three events, the table below demonstrates that they share a common algorithm for enforcement.

 

Table 1. Trigger Identification Matrix
Rule / RV list Table Trigger Constraint Algorithm or Functional Description
BR#1 / RV#1 purchase order Insert date <> SAT or SUN; purchase_amount <= 999.99;
BR#1 / RV#2 purchase order Update date <> SAT or SUN; purchase_amount <= 999.99;
BR#1 / RV#3 purchase order Update date <> SAT or SUN; purchase_amount <= 999.99;

 

Table 2. Trigger Responses
Rule / RV list Constraint Check or Functional Description Response
BR#1 / RV#1 date <> SAT or SUN; purchase_amount <= 999.99; Raise an application error.
BR#1 / RV#2 date <> SAT or SUN; purchase_amount <= 999.99; Raise an application error.
BR#1 / RV#3 date <> SAT or SUN; purchase_amount <= 999.99; Raise an application error.

When programming a significant business requirement, it may result in multiple rule definitions - all to be implemented by one programmer. The programmer normally constructs the RV list by analyzing the rule statements in sequential order. In this case, Tables 1 and 2 are constructed by first analyzing the RV list in sequential order.

When all the triggers are identified in the first sequential pass, the list is sorted by Rule number and RV list number. Then simply reorganize the table by table name and trigger name. The table now identifies algorithms for one or more rules for each trigger. This is illustrated below.

 

>From a system requirement we compose the following business rule statements:
BR#1 <description>
BR#2 <description>
BR#3 <description>

This is followed by an RV list:

RV#1 <description>
. . . . . .
RV#n <description>

A scan of the violation descriptions may produce a list with database tables referenced out-of-sequence:

Rule / RV list Table Trigger
BR#1 / RV#1 purchase order Insert
BR#1 / RV#2 customer Insert
. . . . . . . . .
BR#3 / RV#19 purchase order Insert
BR#3 / RV#20 purchase order Insert

It can be helpful to resort the list by table name, and then by triggering event:

Rule / RV list Table Trigger
BR#1 / RV#1 purchase order Insert
BR#3 / RV#20 purchase order Insert
BR#3 / RV#19 purchase order Update
. . . . . . . . .

Write the Trigger

>From Table 1 we know the actions of the triggers, which are Insert and Update. However, the issue of granularity has been deferred to this point, which is statement level versus row level execution. Whether the trigger is fired at the statement or row level depends mostly upon the data required by the trigger to enforce the rule.

For RV#1, 2 and 3 we must know the purchase date and the purchase amount to enforce the rule. These values are visible during the execution of a row-level trigger. Using Oracle's PL/SQL syntax we can code a row trigger as follows:

 

PACKAGE BODY purchase_order_cons_pkg IS
   PROCEDURE check_rule_1 (purchase_date DATE, purchase_amount NUMBER) IS
   BEGIN
      IF  (purchase_amount             >  999.99
      AND (TO_CHAR(purchase_date,'DY') = 'SAT'
      OR   TO_CHAR(purchase_date,'DY') = 'SUN' )) THEN
          RAISE_APPLICATION_ERROR(.....);
      END IF;
   END check_rule_1;
END purchase_order_cons_pkg;

CREATE OR REPLACE TRIGGER purchase_order_row_iu AFTER Update OR Insert ON purchase_order FOR EACH ROW BEGIN -- Verify that the purchase amount is permitted for this date. purchase_order_cons_pkg.check_rule_1(:new.purchase_date,:new.purchase_amount); END;

This row trigger is fired with the statement: "UPDATE purchase_order SET purchase_amount = purchase_amount + 100." Assuming we have ten purchase orders, the row trigger fires ten times - each time passing one or more current row values as arguments to the purchase order constraints package for verification.

As stated earlier, the specific data requirements of the procedure that enforces the rule determines the timing of the trigger, i.e., statement or row. Consider the rule: The sum of all purchase orders cannot exceed two thousand dollars.

This rule is enforced with a procedure that executes a query on the purchase order table. There are no row-data requirements to the algorithm. Here we use the statement level insert/update trigger. "SELECT SUM(purchase_amount) FROM purchase_order…"

Earlier we mentioned passive versus active triggers. The examples thus far have mostly represented procedural constraints that call RAISE_APPLICATION_ERROR when a constraint violation occurs.

There are cases where a trigger passively overlays a value that is realized at the row level. Consider a rule that automatically applies a discount to a price. The discount rate may be persistent data in another database table. This is illustrated below. The ":old" correlation value is replaced with the ":new" discount value. This type of "replace column value before insert" trigger requires a before-row trigger.

When dealing with many rules, the end result of the trigger code is sequential procedure calls, and the constraints can be easily enforced for any action against one table. The following illustrates a before-row trigger. It's a before row trigger because it overlays the purchase price with a discount price in the first function call to "purchase_order_cons_pkg.offer_discount."

 

CREATE OR REPLACE TRIGGER purchase_order_row_u 
BEFORE Update OF purchase_order FOR EACH ROW
BEGIN
         -- Overlay price with discount. Rule 2
         :old.purchase_amount := purchase_order_cons_pkg.
                                 offer_discount(:old.purchase_date,:old.purchase_amount);

   -- Verify (purchase amount, purchase date). Rule 1
   purchase_order_cons_pkg.check_rule_1(:old.purchase_date,:old.purchase_amount);
END;

In the case of Oracle, when a trigger rule reads or updates a table that is currently being updated - or is being read by Oracle to enforce referential integrity or a delete cascade - the execution of the algorithm must be deferred until the after statement trigger. Ignoring this restriction causes an Oracle run-time table error.

Lets assume we wish to enforce the constraint that protects us from RV#4. We can write a procedure "check_rule_1_av_4." This procedure verifies that: if "old customer shipping status is preferred and new customer shipping status is non-preferred, and old customer has an outstanding purchase order, made on a Saturday or Sunday in excess of 999.99 US dollars" then we have a constraint violation.

Going back to the original purchase order constraints package we have:

 

PACKAGE BODY purchase_order_cons_pkg IS
PROCEDURE check_rule_1 (purchase_date DATE, purchase_amount NUMBER) IS ..
	 
             PROCEDURE check_rule_1_cvl_4 (...) IS
             BEGIN
                SELECT shipping_status_code INTO ss_1 FROM customer WHERE ...
             ...
END purchase_order_cons_pkg;

Consider the condition RV#5. In this case we have a constraint on the purchase order that is an attribute of the customer table. Thus, we can define a procedure, check_1, which takes various arguments: purchase amount, purchase date, customer ID number and shipping status code. It can be an overloaded procedure and/or have default parameters so it can be called from triggers on different tables.

In this case the customer trigger calls the purchase order procedure because this package is responsible for "containing" the constraints on the purchase order entity. Consequently, whenever a customer shipping status is updated there is a constraint check that insures that a pending $999.99 has a preferred shipping status.

Test the Rules

Triggers should normally be tested with a set of SQL statements derived from the RV List. If our list is complete in terms of identifying all events that can break the rule, then we can use this to test our code. This is very easy to do since the RV list is expressed in terms of Insert, Update, and Delete actions against a single entity. The first test would be for RV#1 which is an Insert on a Saturday or Sunday for an amount > 999.99 US dollars. Taken directly from the expression of the RV we can attempt to break the rule with:

 

INSERT INTO purchase_order 
VALUES (..., NEXT_DAY(SYSDATE,'SUNDAY'),..., 501);

For each RV there should be one, possibly two (depending upon how atomic the expression is), SQL statements that can be devised to verify the enforcement. The constraints used to design the code are also used to test it.

Summary

When developing triggers:

  • Ensure completeness of the trigger algorithm by addressing conditions that violate a rule.
  • Remove procedural constraint checks, which are not needed due to design, coding standards, and GUI constraint enforcement.
  • Develop modular code that employs methods of encapsulation - this is the key to maintenance, rule tuning for business changes, and the efficient management of large numbers of rule-based requirements in the server.

Standard citation for this article:


citations icon
Neal A. Fishman , "Server-based Rules Enforcement" Business Rules Journal Vol. 1, No. 5, (May 2000)
URL: http://www.brcommunity.com/a2000/b018.html

About our Contributor:


Neal  A. Fishman
Neal A. Fishman Enterprise Architect, Equifax

Neal A. Fishman is an Enterprise Architect with Equifax. He has 20 years of experience in Data Processing. He is heavily involved in the architecture, design, and performance tuning of databases in multi-tier heterogeneous web, client/server, data sharing and data warehousing environments.

He serves as a member of the Business Rules Group, and is a former member of the IEEE IDEF1X (IDEFobject) Standards Committee. He is an ExperNet expert for Giga and a Practicing Member of the World Wide Institute of Software Architects. He is certified by IBM as a developer and DBA in DB2 UDB, and is
currently a board member of the Atlanta Chapter of DAMA.

He has delivered presentations in North America, Australia, and Europe. He has been published in Database Programming & Design, SQL Server Professional, DM Review magazine, and The DataToKnowledge Newsletter. He serves as Technology Review Editor for BRCommunity.com.

Read All Articles by Neal A. Fishman

Online Interactive Training Series

In response to a great many requests, Business Rule Solutions now offers at-a-distance learning options. No travel, no backlogs, no hassles. Same great instructors, but with schedules, content and pricing designed to meet the special needs of busy professionals.