Server-based Rules Enforcement
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.
Taking Action
If a trigger detects
a violated business rule, it can be coded to act in a number of ways.
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.
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 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#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:
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 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):
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.
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.
This is
followed by an RV list:
A scan of
the violation descriptions may produce a list with database
tables referenced out-of-sequence:
It can be
helpful to resort the list by table name, and then by
triggering event:
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:
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."
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:
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:
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:
When
Granularity
Statement Type
Before
Statement
Insert, Update,
Delete
After
Statement
Insert, Update,
Delete
Before
Row
Insert, Update,
Delete
After
Row
Insert, Update,
Delete
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?
BR#1.
A purchase order that originates on a Saturday or Sunday cannot
have a purchase amount that exceeds 999.99 US dollars.
RV#1. Insert
a purchase order with a purchase date of Saturday or Sunday and
a purchase amount that exceeds 999.99 US dollars.
The following
illustrates an RV list when the constrained and constraining
entities are not in the same table.
CREATE TABLE customer (customer_id NUMBER PRIMARY KEY,
shipping_status_code CHAR(2) NOT NULL ...
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.
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;
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.
>From a
system requirement we compose the following business rule
statements:
BR#1
<description>
BR#2
<description>
BR#3
<description>
RV#1
<description>
.
. .
.
. .
RV#n
<description>
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
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
.
. .
.
. .
.
. .
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_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;
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;
INSERT INTO purchase_order
VALUES (..., NEXT_DAY(SYSDATE,'SUNDAY'),..., 501);
Standard citation for this article:
About our Contributor:
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.