Unlike many rules you might encounter in business, this actually makes sense! Simple as it is, though, it isn't possible to enforce at the database level without the use of a trigger. Now enhance this rule to include the following conditions:

If an employee is switching from salary to an hourly rate, the new yearly income, based on a 2000-hour year, must be within plus-or-minus 20 percent of his salary. The reverse condition applies to employees switching to a salary from an hourly rate.

Listing 11.3 shows one possible implementation of these rules using a before-update trigger on the employee table.

INPUT/
OUTPUT
Listing 11.3. Trigger enforcing complex business rules.

 1: CREATE OR REPLACE TRIGGER emp_pay_type_chg
 2: BEFORE UPDATE ON employee
 3: FOR EACH ROW WHEN (OLD.pay_type <> NEW.pay_type)
 4: DECLARE
 5:   new_yearly_rate       employee.pay_rate%TYPE;
 6:   old_yearly_rate       employee.pay_rate%TYPE;
 7:   allowed_variance_amt  employee.pay_rate%TYPE;
 8:   allowed_variance_pct  NUMBER(2,2) := .20;
 9:   hours_in_year         INTEGER := 2000;
10: BEGIN
11:   --Check to see if the pay rate has also been changed.
12: IF :OLD.pay_rate = :NEW.pay_rate THEN
13:     RAISE_APPLICATION_ERROR (-20000,
        Â'The pay type changed and the pay rate did not.');
14: END IF;
15:
16:   --Check to verify that the new expected yearly rate is within
17:   --plus-or-minus 20% of the previous yearly rate.
18:   IF :OLD.pay_type = `H' AND :NEW.pay_type = `S' THEN
19: --the new salary must be + or - 20% of the old hourly rate * 2000 hours.
20: old_yearly_rate := ROUND(:OLD.pay_rate * hours_in_year,2);
21:     new_yearly_rate := :NEW.pay_rate;
22:     allowed_variance_amt := allowed_variance_pct * old_yearly_rate;
23:  IF ABS(new_yearly_rate - old_yearly_rate) > allowed_variance_amt THEN
24:       RAISE_APPLICATION_ERROR (-20000,'New salary is not within `
25:         || TO_CHAR(allowed_variance_pct * 100) || `% of the old rate.');
26:     END IF;
27:   ELSIF :OLD.pay_type = `S' AND :NEW.pay_type = `H' THEN
28: --the new hourly rate * 2000 hours must be + or - 20% of the old salary.
29: new_yearly_rate := ROUND(:NEW.pay_rate * hours_in_year,2);
30:     old_yearly_rate := :OLD.pay_rate;
31:     allowed_variance_amt := allowed_variance_pct
        Â * (old_yearly_rate * hours_in_year);
32: IF ABS(new_yearly_rate - old_yearly_rate) > allowed_variance_amt THEN
33:       RAISE_APPLICATION_ERROR (-20000,
          Â'New project yearly rate is not within `
34: || TO_CHAR(allowed_variance_pct * 100) || `% of the old salary.');
35:     END IF;

                                                  continues

Listing 11.3. continued

36:   ELSE
37:     --Something is very wrong if we ever get here.
38:     RAISE_APPLICATION_ERROR(-20000,'Invalid combination of pay types: `
39:                             || :OLD.pay_type || ` ` || :NEW.pay_type);
40:   END IF;
41: END;
42: /
43: Trigger created.

ANALYSIS
The trigger shown in Listing 11.3 is implemented as a before trigger (line 2) so that it can validate any changes to the employee record before they are actually saved in the database. The WHEN condition in line 3 causes this trigger to fire only when the pay type is changed. This trigger could be fired on every update, but that would decrease efficiency. Because the trigger validates changes to the pay_type field, it makes sense to fire it only when that particular field is changed.

The first, and easiest, test that this trigger performs is to verify that the pay_rate field has been changed (line 12). An error is raised (line 13) if the pay rate has not been changed. The next task is to check to be sure that the employee's new yearly income will be within 20 percent of its previous values. A value of 2000 hours (line 9) is used to compute the yearly wages for an hourly worker. Lines 18 through 26 check the new wage for an employee changing from an hourly rate to a yearly salary. If the new salary is not within 20 percent of what the employee had been making previously, then an error is raised (lines 24_25). The code in lines 27 through 35 makes a similar check for a salaried employee being switched to an hourly rate. Finally, the code in lines 37 through 39 is executed if the change is not one of these two cases, and an error is raised.

To test the trigger shown in Listing 11.3, try typing in the SQL INSERT and UPDATE statements shown in Listing 11.4.

INPUT/
OUTPUT
Listing 11.4. Testing the pay_type_chg trigger.

 1: --Insert an employee record and try some updates that should fail.
 2: --Then try an update that should succeed.
 3: --Create an employee for testing.
 4: INSERT INTO employee
 5:     (emp_id, emp_name, pay_rate, pay_type)
 

 6:     VALUES (100,'Kazam el Shabar',100,'H');
 7: 1 row created.
 8: --Try updating to an invalid pay type.
 9: UPDATE employee
10:     SET pay_type = `X',
11:         pay_rate = 200000
12:     WHERE emp_id = 100;
13: UPDATE employee
14: *
15: ERROR at line 1:
16: ORA-20000: Invalid combination of pay types: H X
17: ORA-06512: at "MY_READER.EMP_PAY_TYPE_CHG", line 34
18: ORA-04088: error during execution of trigger `MY_READER.EMP_PAY_TYPE_CHG'
19: --Try an update where we only update the pay type and not the rate.
20: UPDATE employee
21:     SET pay_type = `S'
22:     WHERE emp_id = 100;
23: UPDATE employee
24: *
25: ERROR at line 1:
26: ORA-20000: The pay type changed and the pay rate did not.
27: ORA-06512: at "MY_READER.EMP_PAY_TYPE_CHG", line 10
28: ORA-04088: error during execution of trigger `MY_READER.EMP_PAY_TYPE_CHG'
29: --Update both type and rate, but the new  yearly rate is
30: --more than 20% greater or less than the previous rate.
31: UPDATE employee
32:     SET pay_type = `S',
33:         pay_rate = 300000
34:     WHERE emp_id = 100;
35: UPDATE employee
36: *
37: ERROR at line 1:
38: ORA-20000: New salary is not within 20% of the old rate.
39: ORA-06512: at "MY_READER.EMP_PAY_TYPE_CHG", line 20
40: ORA-04088: error during execution of trigger `MY_READER.EMP_PAY_TYPE_CHG'
41: --Update both type and rate, this time the new value is within range.
42: UPDATE employee
43:     SET pay_type = `S',
44:         pay_rate = 240000
45:     WHERE emp_id = 100;
46: 1 row updated.

ANALYSIS
The first statement, the INSERT statement, simply adds an employee record to the table for testing purposes. The first UPDATE statement tries to set the pay type code to `X'. Because it is the pay type field that is changing, the pay_type_chg trigger fires and rejects the change because `X' is an invalid code. The second UPDATE statement changes the pay type to salaried, but does not change the pay rate. This update is also rejected, this time because the rate has not also been changed. The business rule states that the new yearly pay must be within 20 percent of the previous value when an employee's pay type changes, and the next two examples deal with this. Using a value of 2000 hours in a year, and multiplying that by the employee's current rate of $100/hour, you get a total of $200,000 per year. Twenty percent of $200,000 is $40,000, so the valid range after changing the pay type would