FRM105 Define a Trigger Function

Show Contents List

FRM105 - Define a Trigger Function

Exercise Concept

There is a business requirement that any salary increase which exceeds 1,000 must be approved by the HR Manager.

A salary change could be made via more than one application.

The solution is a file level trigger function, which runs before update, if the new salary exceeds current salary by more than 1,000.

The trigger function, resets salary to its previous value, and sends a change request email to the HR Manager.

The HR Manager makes a salary increase of more than 1,000 by changing Business Phone Number to A100 while changing salary. For this exercise, the Business Phone is used as a spare field.

The trigger function allows an increase of 1,000+ if Business Phone is A100. It also resets Business Phone to N/A in this case.

In a real application the email message could contain the URL of a web application (a web page) which the HR Manager simply clicks on to run and make the authorized change. To simplify this exercise you will make the change via a simple form.

Objectives

To define a trigger for field salary on the file PSLMST

The trigger event will be Before Update

The trigger condition will be Salary greater than previous Salary

The trigger function will send an email requesting change approval, if the increase in salary is greater than 1,000 and the business phone number is not equal to A100

On receiving the email message, the salary increase will be made by the 'HR Manager' using a form. In a real application a Web Page could be provided which may be executed using a URL in the email message, to confirm the salary change. A salary change greater than 1,000 is made by making Business phone number equal to A100 to indicate that the salary change is confirmed.

To achieve these objectives you must complete the following:

Step 1. Create a Trigger Function

Step 2. Define a Salary Trigger for Employee File (PSLMST)

Step 3. Create an Employee Salary Change Form

Step 4. Test your Employee Salary Trigger

Show Contents List