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. We are treating Business Phone as a 'spare field' for this exercise.
- 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 WAM) 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 WAM 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