Triggers - A Classic Example

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

...

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".

...

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.

...

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.

...

         SALARY    NEP   SALARY 
i.e.      salary is not equal to previous salary

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

...

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:

...