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:




/* 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.                                   */




/* Now examine exactly what event has occurred              */






/* A new employee is being created */




      << calculate correct value into field WEEKPAY >>



/* An existing salary has been changed */







      << calculate correct value into WEEKPAY >>

      << using OLDSALARY in the calculations  >>




      ABORT MSGTXT('WEEKPAY trigger function invalidly invoked')







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:


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: