Triggers - A Classic Example

The following example is a classic example of how a trigger function should be used.

It takes a complex business rule and "encapsulates" it into a trigger.

Next the trigger is linked to the associated database table and the business rule is performed automatically whenever the specified event occurs.

It is a classic example because it clearly demonstrates how triggers can "encapsulate" complex rules and associate them directly with the "object" (i.e. table).

The Business Problem

ACME Engineering run a payroll system.

The Employee Master table (EMPL) contains two columns called "SALARY" and "WEEKPAY".

SALARY is the annual salary that the company has contracted to pay the employee.

WEEKPAY is the amount paid to the employee each week.

WEEKPAY is arrived at via a complex set of rules.

For a new employee the WEEKPAY calculation is relatively simple, but when an employee's SALARY is changed the complex calculation involves both the new SALARY figure and the previous SALARY figure.

The Trigger Function

The first step in defining the trigger is to define the trigger function that encapsulates all the WEEKPAY rules into one and only one place.

This is a fundamental of good trigger design.

The following function may have been coded to handle this:

FUNCTION OPTIONS(*DIRECT *NOMESSAGES *MLOPTIMIZE)

         RCV_LIST(#TRIG_LIST) TRIGGER(*FILE EMPL)

 

/* Define the standard trigger list which will contain the */

/* before and after images of the EMPL table record. These  */

/* fields are automatically added to the list definition   */

/* by the RDML compiler.                                   */

 

DEF_LIST NAME(#TRIG_LIST) TYPE(*WORKING) ENTRYS(2)

 

/* Now examine exactly what event has occurred              */

 

DEFINE FIELD(#OLDSALARY) REFFLD(#SALARY)

 

CASE  OF_FIELD(#TRIG_OPER)

 

/* A new employee is being created */

 

WHEN  VALUE_IS('= BEFINS')

      GET_ENTRY NUMBER(1) FROM_LIST(#TRIG_LIST)

      << calculate correct value into field WEEKPAY >>

      UPD_ENTRY IN_LIST(#TRIG_LIST)

 

/* An existing salary has been changed */

 

WHEN  VALUE_IS('= BEFUPD')

      GET_ENTRY NUMBER(2) FROM_LIST(#TRIG_LIST)

      #OLDSALARY := #SALARY

 

      GET_ENTRY NUMBER(1) FROM_LIST(#TRIG_LIST)

      << calculate correct value into WEEKPAY >>

      << using OLDSALARY in the calculations  >>

      UPD_ENTRY IN_LIST(#TRIG_LIST)

 

OTHERWISE

      ABORT MSGTXT('WEEKPAY trigger function invalidly invoked')

 

ENDCASE

 

#TRIG_RETC := OK

RETURN

 

Activating the Trigger Function

Now that the trigger function has been defined it needs to be activated. To do this, access the definition of table EMPL and associate two trigger invocation events with it.

The first would be specified as "BEFORE INSERT" and would not have any associated conditions. This means that the trigger function will be called whenever an attempt is made to create a new employee.

The second would be specified as "BEFORE UPDATE" and would have an associated condition which would look something like this:

         SALARY    NEP   SALARY 

i.e.      salary is not equal to previous salary

 

which says that the trigger should be activated "BEFORE UPDATE" but only if the employee's SALARY has changed.

Defining the "BEFORE UPDATE" event like this is very efficient because it means that the trigger will not be activated when the employee's salary has not been changed (which will probably be most of the time).

If WEEKPAY had to be recalculated when the SALARY changed or when the COMPANY that the employee worked for changed, then you would define the invocation event like this instead:

         SALARY    NEP   SALARY     OR  COMPANY   NEP   COMPANY 

i.e.      salary is not equal to previous salary

    or   company is not equal to previous company

 

If WEEKPAY was to always be recalculated, then you would not have to define two separate invocation events. You could simply define one event (with no conditions) and indicate that the trigger should be invoked "BEFORE INSERT" and "BEFORE UPDATE".

Of course, this means that every single insert or update of an employee would cause the trigger function to be invoked.

Key Things to Note About this Example

This example demonstrates some of the key elements of good trigger design and use:

• The "encapsulation" principle. The WEEKPAY calculation "method" is "encapsulated" in one and only one function. If it has to be changed it only has to be changed in one place.

• Deferment. The existence of the WEEKPAY method does not have to be defined, or even known about, during initial system design.
  
  This also means that a "method" can be introduced into an application design at any time. For instance, the WEEKPAY method does not have to be defined before any applications that create or update employees are. The create and update applications can be defined and tested first. When the WEEKPAY method is created and defined it will immediately begin to affect the processing of all existing applications.

• Reusability. The WEEKPAY calculation method is automatically and implicitly reused by any application that creates or changes employee details. The trigger could be activated from a normal NPT device via an "Employee Maintenance" function, or from a PC application via the LANSA Open facility.

• Transparency. The fact that the WEEKPAY logic is present and being used is invisible and probably immaterial to an RDML builder creating an "Employee Maintenance" function.

• Separation of the "method" from the "event". The trigger function defines what to do when an "event" happens (i.e. the "method").
  
  However, it does not have to detect the occurrence of the event.
  
  For example, the function defined previously defines a "method" called "Calculate Weekly Pay".
  
  The business rules says that weekly pay must be (re)calculated when a new employee is taken on, or when an existing employee's salary is changed, or when an existing employee moves to another company.
  
  The actual "event" is defined in the LANSA data dictionary.