Generating Triggers That Enforce Your Business Rules

David E. Johnson, The United Illuminating Company

Abstract

Designer's Table APIs have introduced many of us to the concept of enforcing business rules in the database. However, not everyone has access to a tool like this, and even if we do there are a number of concerns that it doesn't satisfy. A low cost alternative to Designer will be presented that generates business rules validation code for each of your tables using a pattern based generation engine. We will be using SQL*Plus, SQL*Loader, and a text editor to implement this highly customizable solution to capturing, analyzing, and generating your business rules. The mechanism used by the engine will give you the capability of testing your rules separately from your table or application. It will provide a capability of developing your rules with a stepwise refinement: Analyze, Design, Code, Test, and Go. You will have much greater control over when your rules will fire. You will be able to defer rule processing to a pre-commit phase, in order to process rules that are based upon transactions rather than statements. At run time you can have the option of turning off one rule without disabling all triggers.

Introduction

Oracle Designer’s Table API’s can be used to enforce some simple business rules, but I’ve found that trying to extend this functionality is sometimes difficult. They also generate null procedure stubs for functionality that is not being used, and generate a lot of code that is hard to read.

The CDM Ruleframe module of Designer Headstart adds some flexibility at a cost, but even this does not give control over when a rule is enforced.

While it is possible to turn off all rules by disabling triggers, this affects all open sessions. Many times I have needed to run a batch process during the day, while normal OLTP processes are running simultaneously. If the batch process needs to override some of the normal functionality, the triggers would have to be disabled, and normal use of the system would have to be prevented.

Writing custom code for each set of rules can produce the desired results, but can be time consuming and complex if a formal methodology is not followed.

How do we accomplish incorporating business rules into the server without creating code streams that will create a maintenance nightmare? I propose that we define patterns of code that can be used repeatedly throughout all of our structures. Once the pattern is determined we add functionality by building into this pattern the rules and services we require to implement our application. If we find repeatable patterns within our rules we can either create packaged functions or procedures which implement the rules pattern, and call these from inside our rule code, or if the pattern is more dynamic, we can develop mini code pattern generators which we use to extend our model. Rules should be implemented in a discrete manner, so that each rule generates a distinct procedure that can be unit tested, and incorporated into the system in a modular fashion. In this way, when rules change we can easily identify the areas of change, or add and delete rules as needed, and regenerate the application.

This paper describes my implementation of this functionality, which I have called ‘brGen the Business Rules Generator’. It is intended to be a continuous work in progress, to be able to adapt to changes in Best Practice as time goes by. I have used this framework in a production schema that consists of about 60 tables with over 450 business rules implemented. The version I used to do this generated only stub procedure bodies for each rule, and required hand coding to complete. The version I am presenting now has only been used against test schema, but it has achieved 100 percent generation. There are some future enhancements that need to be addressed, but I believe that the system is useful now.

Theory of Operation

Overview

Database table event triggers are used to call the package that will invoke your rules. The only job of these triggers is to pass control to the table’s rule validation package. The triggers should never need to change unless you add or remove columns from the table. All rules are invoked from one of the Processes within this package.

All rules will be invoked from a single package. Each rule will have a single function that evaluates the constraint violation list or CVL (the conditions which may cause a rule to be violated or invoked) for that rule. All of these CVLs will be stored in one package per table. Each rule will also have a procedure that implements it, and reports violations to the Errors stack. All the rule procedures will be stored in one package per table. By separating the CVL and process logic for each rule into distinct processes, it becomes possible to test each rule, and its CVL logic individually, making the system more maintainable.

Rule violations are reported to an Errors Stack in the form of messages written in Business English. End User Applications may use this stack to display messages back to the user. Error evaluation for exception handling is deferred until all rules have been evaluated whenever possible. This allows the message feedback to include all violations in one shot to avoid user frustration.

Each table will have one package that maintains the status of its rules, and the current operation being performed.

If the table has any rules that will be triggered after the row operation is completed, there will be a package generated to maintain the row data stack. This allows rules to be enforced that might otherwise cause mutating table errors, and also allows row data to be available to rules that are enforced before or after committing a transaction.

Many Oracle web applications run more effectively when table DML is abstracted through the use of a table API which provides Insert, Update, Delete, Select, and Lock methods which are called by the application, rather than issuing SQL statements. The generator provides a basic API package with limited functionality, but you may expand on this if you prefer. The generation of a table API is optional.

DML Processing (Insert, Update, Delete)

Before each statement, the operation type is passed to the validation package Init_Statement Procedure, which applies any global rule settings to the current table. If rule validation has not been disabled, it checks to see if any transaction has been initialized. If not, it registers itself to the Transactions package as the initiator of this transaction and invokes any PRE_TRANSACTION rules it may find for the table. Next, it checks whether there are any post processing rules. If so, it pushes the table name onto the transactions table stack and stores a savepoint on the table’s row data stack. Next, it stores the operation and invokes any PRE_STATEMENT rules.

Before each row, if rule validation has not been disabled, the :new and :old record, and the rowid are passed to the Pre_Process function which returns any transformations into the :new record before posting the row. Within this function we check to see if the statement has been initialized, if not, we initialize the statement, and invoke any PRE_FIRST_ROW rules. Then we invoke any PRE_ROW rules. Next, it checks whether there are any post_processing rules. If so, it pushes the transformed row data onto the tables data stack (new, old, and rowid).

After each statement, if rule validation has not been disabled, the Post_Statement procedure is invoked. The stack is prepared, and we loop through the rows in the statements row stack and invoking each of the POST_ROW rules. After this each of the POST_STATEMENT rules are invoked. Then the state package is called to set up for the Next_Statement. If this statement initiated the transaction, then call the Transactions Pre_Commit procedure.

Transaction Control

In order for an application to control its own transactions and take advantage of the ability to defer rule invocation until commit time, it needs to call the Transactions package Init procedure with a token that is not equal to the name of any table.

When the transaction is ready to be committed, the application should call the Transactions package Xcommit procedure. This will dynamically call back the Pre_Commit procedure of each table that registered itself to the Transactions package. When this is completed, the transaction will be Committed, and the tables’ Post_Commit procedures will be invoked in the same manner. The table’s validation package Pre_Commit procedure loops through the row data stack and invokes each PRE_COMMIT rule. The table’s validation package Post_Commit procedure loops through the row data stack and invokes each POST_COMMIT rule.

Figure 1. brGen ERD

Entity / Table Definitions

RULE_STAGE -- Staging area for loading rules into brGen, each developer has

his own copy of this table.

Attributename Datatype Length Dec. Domain Default Description

------------- -------- ------ ---- --------------- ----------- --------------------------------

Id NUMBER 10 2 Id Sequence Generated Id

Text VARCHAR2 1023 0 Free_Text A line of text from the

Loaded File.

RULES -- Business Rules for each table.

Attributename Datatype Length Dec. Domain Default Description

------------- -------- ------ ---- --------------- ----------- --------------------------------

Owner VARCHAR2 30 0 Ora_Object_Name Owner of Table or Process

Process_Table VARCHAR2 30 0 Ora_Object_Name Table or Process to be Generated

Rule_Function VARCHAR2 30 0 Ora_Object_Name Name of this Rule Process

Rule_Timing VARCHAR2 30 0 Rule_Timing 'NEVER' When will this process be

evaluated?

Position NUMBER 10 2 Seq_In_Parent 1 Sequence of Evaluation

Rule_Type VARCHAR2 30 0 Rule_Type 'V' Type of Rule Process

( Service, Transformation,

or Validation )

Version NUMBER 10 2 Version 1 Number of times this record was

inserted, or modified

Status VARCHAR2 1 0 Record_Status 'A' Status of this Record

(Active / Inactive )

Modified_By VARCHAR2 30 0 Modified_By user Last User to Insert or Update

this Record

Modified_Date DATETIME 0 0 Modified_Date sysdate Timestamp of last event

on this record

Rule_Comment VARCHAR2 1023 0 Free_Text Inline Comment for

Rule Invocation

RULE_TEXT -- Text associated with a rule

Attributename Datatype Length Dec. Domain Default Description

------------- -------- ------ ---- --------------- ----------- --------------------------------

Owner VARCHAR2 30 0 Ora_Object_Name Owner of the table or process

Process_Table VARCHAR2 30 0 Ora_Object_Name Table that owns the Rule

Rule_Function VARCHAR2 30 0 Ora_Object_Name Name of the Rule Process

Text_Type VARCHAR2 30 0 Rule_Text_Type 'RULE' Type of Text stored in this row

Line NUMBER 10 2 Seq_In_Parent 1 Sequence of Line within the Text

Control VARCHAR2 1 0 Text_Or_Pattern 'T' This line is Text or a Pattern

Text VARCHAR2 1023 0 Free_Text The Text for this Line

Domain Definitions

Free_Text

Description Free Format Text Field

Datatype VARCHAR2(1023)

Check

Default

Id

Description Unique Sequence generated Id.

Datatype NUMBER(10,2)

Check

Default

Modified_By

Description Last User to Modify this Record

Datatype VARCHAR2(30)

Check

Default user

Modified_Date

Description Timestamp of Last Modification.

Datatype DATE

Check

Default sysdate

Ora_Object_Name

Description Oracle Object Name

Datatype VARCHAR2(30)

Check

Default

Record_Status

Description The Status of this Record ( Active / Inactive )

Datatype VARCHAR2(1)

Check VALUE in ('A','I')

Default 'A'

Domain Items

ItemName Description

--------------- -----------

A Active

I Inactive

Rule_Text_Type

Description Usage of this Text

Datatype VARCHAR2(30)

Check VALUE in ('RULE','CVC','PROC')

Default 'RULE'

Domain Items

ItemName Description

--------------- ------------------------------------------------------------------------

RULE The Rule, Expressed in Business English

CVC A constraint Violation Condition, part of the Constraint Violation List.

PROC The Procedure that validates or transforms the data.

Rule_Timing

Description Timing of Event which triggers Rule Evaluation.

Datatype VARCHAR2(30)

Check VALUE in ( 'NEVER' ,'PRE_TRANSACTION' ,'PRE_STATEMENT'

,'PRE_FIRST_ROW' ,'PRE_ROW' ,'POST_ROW' ,’POST_STATEMENT'

,'PRE_COMMIT_EACH_ROW','PRE_COMMIT','POST_COMMIT_EACH_ROW','POST_COMMIT' )

Default 'NEVER'

Domain Items

ItemName Description

-------------------- --------------------------------------------

NEVER Not Evaluated

PRE_TRANSACTION At Start of Transaction

PRE_STATEMENT At Start of Each Statement

PRE_FIRST_ROW Before the First Row of a Statement

PRE_ROW Before Each Row of a Statement

POST_ROW After Each Row of a Statement

POST_STATEMENT After Each Statement

PRE_COMMIT_EACH_ROW Before Committing, Process once for each row

PRE_COMMIT Before Committing Each Transaction

POST_COMMIT_EACH_ROW After Committing, Process once for each row

POST_COMMIT After Committing Each Transaction

Rule_Type

Description Type of Rule (Service, Transformation, or Validation)

Datatype VARCHAR2(30)

Check VALUE in ('S','T','V')

Default 'V'

Domain Items

ItemName Description

--------------- -----------

S Service

T Transformation

V Validation

Seq_In_Parent

Description A unique sequence within a set of rows determined by a

multi_segmented unique constraint.

Datatype NUMBER(10,2)

Check

Default 1

Text_Or_Pattern

Description Defines whether the text column should be treated as

text or a call to a pattern procedure.

Datatype VARCHAR2(1)

Check VALUE in ('T','P')

Default 'T'

Domain Items

ItemName Description

--------------- ----------------------------------------------

T The Text field is Literal.

P The Text field Invokes a pattern procedure.

Version

Description Number of times this Record has been Modified

(including Insertion).

Datatype NUMBER(10,2)

Check

Default 1

Description of Generator Packages

brLoad.bat Batch file and brLoad.par Parameter File. – These files are used to invoke SQL*Plus and SQL*Loader to truncate the Rule_Stage table, load the current rules file into the staging table, and invoke the parsing process.

brParse Package. – The Rule loading process. Parses data in the local Rule_Stage table. Each developer has his own local copy of the staging table, and parser package.

brGen Package. – The main code stack and generation engine. This package manages the code stack, and creates column and key lists for the tables’ objects, it also contains the code generator procedures.

Comments Package. – Support for conditional comments and multi-line comments. This package is used to allow us to change our generated code based upon target database version, debugging mode, and the presence of certain types of rules, or any other condition you want to program in. These comments can be stripped out by the generator, to keep your code trim.

db_Version Package. – Supports the Comments Package.

Test Package. – Supports the Comments package, by setting debug mode, also sets tracing level, for detailed logging of rule handling.

pr Package. – The main patterned printing engine. This package contains the primary interface to the code stack ( the l command. i.e. pr.l(‘dbms_output.put_line(‘‘Hello World!’’)’); ), as well as list processing commands like foreach.

Description of Support Packages (not generated)

The following packages support the functionality of the rules engine:

Rule_Status Package. -- This package adds support for changing the enablement status for all table rules.

Errors Package. -- This package holds the rule violation stack, and controls raising exceptions.

Log Package. -- This package holds the trace message stack.

Change Package. -- The functions of this package determine whether a column was inserted, nullified, or modified.

Transactions Package. -- This package supports the functionality that registers tables that are part of a transaction, and performs a callback to perform pre-commit and post-commit rule invocations.

Description of Rules Generation Output

Triggers -- for each table we will generate three triggers:

Before Statement on Insert, Update, or Delete. -- This will initialize each statement, and each transaction not previously registered.

Before Each Row on Insert, Update, or Delete. -- After checking whether all rules have been disabled, this trigger will pass the row new and old values to the validation package, which will perform any pre-row modifications to data, or validations.

After Statement on Insert, Update or Delete. -- After checking whether all rules have been disabled, this trigger will process any after row or after statement rules. If this statement registered the transaction, it will also run any pre-commit rules.

Generated Packages. -- For each table we will generate up to seven packages:

Validation Package (_VAL). -- This package contains the procedures that are invoked by the above triggers, and by the transaction control package. These procedures invoke the individual rule procedures when the corresponding constraint violation list functions indicate that the rule could have been violated, if the rule has not been disabled.

State Package (_STT). -- The procedures and functions in this package maintain the state of the rule engine, they also store the operation and mode which invoked the statement.

Stack Package (_STK). -- The procedures and functions in this package maintain the row stack for all statements for this table in the transaction, they also handle transaction logic, such as savepoints, and rollbacks. This package is only generated if there are post-processing rules.

Constraint Violation Lists Package (_CVL). -- The functions of this package determine whether a rule could have been violated.

Rules Package (_RUL). -- The procedures in this package implement the corresponding rules.

Services Package (_SVC). -- The functions in this package are general-purpose functions used by the engine, and rules to navigate relationships to enforce multi-table rules, to identify a particular record, or to retrieve a rowid. Additional service routines may be defined.

API Package (_API). -- The functions and procedures in this package supply a basic API for DML on the table, supporting Insert (ins), Update (upd), and Delete (del) procedures, and Select (sel), Lock (lck), Query By Example (qbe), and Fetch functions. The generation of the API package is optional.

Capturing Business Rules

So, how do we get the data into the tables anyway? Well I took the approach that a fancy front end might actually be less friendly than a very simple method. While I haven’t built any graphical interface, I suppose someone could use TCL, VB or even Microsoft Access to kick off my little batch script brLoad.bat. this takes one command line argument, which is the file name of the file we want to load. This copies whatever filename you are using to ‘brLoad.dat’, it then runs a sql*plus script to truncate the users Rule_Stage table, and runs sql*loader to capture the contents of your file into the Rule_Stage table.

When we are satisfied with the results of the load, we run the brParse.bat script to parse the staging table, and load the rules and rule_text tables. Parse, you say, what is that? Well in order to encode this into a somewhat free-form text file, I came up with a little markup language to help the parser determine what you are trying to do. I’ve called this language BRML. It is defined below:

BRML Tags

· [brml] , [/brml] -- Start and End tags for the file. Text that comes before or after these tags will be ignored, and can be used for any kind of additional documentation, or comments. These tags may appear only once within the document.

· [context owner=schema proc=Process_Table],[/context] -- The parse routine will save the most recently used owner and process_table names. If no owner is ever supplied, the owner will default to the current user, once an owner is given, it will be assumed until a new owner clause is supplied within another context tag. The ‘process_table’ key word may be abbreviated to ‘proc’. The end context tag is optional.

· [rule name=rule_function timing=Rule_Timing type=rule_type pos=Position ],[/rule] – The rule tag is subordinate to the context tag, the position, timing, and type clauses are optional. The end rule is optional, and will be assumed if a new rule or context tag is encountered.

· [rem]Rule_Comment\n or #Rule_Comment\n -- The REM or Rule Comment Tag is subordinate to and should immediately follow the rule tag (on the next line), it is completed by a newline( \n ), so no end tag is required. Only the first REM tag will be recognized for each Rule Tag.

· Free text following the Rem Tag (if any) on subsequent lines will be assumed to be the Business English Rule Text

· [cvl],[/cvl] – The Constraint violation list tag begins the constraint violations list. The end tag is optional.

· [cv trace=trace_clause ],[/cv] – The Constraint Violation Tag is subordinate to the CVL tag. The end tag is optional. Each cv tag should represent a condition statement, all cv conditions will be or’ed together. The purpose of the cv statement is to allow trace logging during system test. This is a reserved feature, and will not be implemented until the next version.

· [proc],[/proc] – The Proc Tag is the text of the procedure that will implement the rule.

· [pat]Pattern_Call\n or ~Pattern_Call\n – the pattern tag is a dynamic call definition to any procedure which will generate a specific pattern of code to the brGen Code Stack. This is one of the features that gives real power to the brGen Application, As it allows dynamic generation of reusable rule code fragments to the rule definition. Think of it as a type of compiler directive or pragma. The pattern tag is valid inside of either the CVL or PROC tags.

Unlike HTML, all BRML tags must be the first text on the line. With the exception of the rem and pattern tags and their aliases, they should be the only text on the line, all other text on the line will be treated as a comment.

So far there is no functionality for modifying existing rule text, the parser will delete and reinsert any rules it finds by section, replacing all text within a text type. Within the rule tag, text types are positional and must come in the following order: Rem Tag, Free Text (the Business Rule), CVL Tag, Proc Tag. This is a limitation of my implementation of the parser. However any section may be skipped, so it is possible to replace only the sections that appear.

About the Author

David Johnson is a senior DBA/architect and Designer Repository administrator for The United Illuminating Company, a local power distribution company located in New Haven, Connecticut. He has been developing with Oracle tools for six years.