be $160,000_$240,000 inclusive. The third UPDATE statement attempts to change the employee's pay rate to a salary of $300,000. This is outside the valid range, so the update is rejected. The final update also changes the pay type to salaried, but at a rate of $240,000 per year. This is within the 20 percent range, so the trigger allows the update.

Maintaining Data Integrity

Another common use for triggers is to assist in maintaining the integrity of the data stored in the database. Suppose that you wanted to store a count of the number of employees in each department and that you wanted to store this count in the department table. You would first add an employee count field to the department table. To do so, enter and execute the input shown in Listing 11.5.

INPUT/
OUTPUT
Listing 11.5. Adding an employee count field to the department table.

ALTER TABLE department
  ADD (no_of_emps     NUMBER(38));
Table altered.

ANALYSIS
The ALTER TABLE command in Listing 11.5 adds one numeric field to the department table, which will be used to contain a current count of employees assigned to each department.

The department table in the sample database should now look like this:

SQL> describe department
 Name                            Null?    Type
 ------------------------------  -------- ----
 DEPT_ID                         NOT NULL NUMBER(38)
 DEPT_NAME                                VARCHAR2(32)
 NO_OF_EMPS                               NUMBER(38)

The no_of_emps field is used to keep track of the number of employees in any given department. Think of how this employee count could be maintained. One possible solution would be to have any program that adds an employee, deletes an employee, or changes an employee's department assignment to update this value appropriately. This would work as long as the programs always worked correctly, and as long you never forgot that the no_of_emps value needed to be maintained. Unfortunately, as you add programmers to a project and as the number of programs that need to maintain this value increases, the likelihood of a mistake also increases. Wouldn't it be nice if there were one central point where you could code the logic to maintain the employee count for a department? Well, there is a way. Placing the code to maintain the employee count into a trigger gives you a central point of control and has the added benefit of reducing the burden on the programmers, who no longer must worry about this issue at all.

Writing the code to deal with this issue is a bit more complicated than the previous example regarding changes to an employee's pay type. To maintain the employee counts, you will need to write an insert trigger, an update trigger, and a delete trigger. Table 11.3 shows what each trigger will need to do.

Table 11.3. Triggers needed to maintain employee counts.

Listing 11.6 shows the code to create the three triggers needed to maintain employee counts for each department.

INPUT/
OUTPUT
Listing 11.6. Triggers to maintain departmental employee counts.

 1: CREATE OR REPLACE TRIGGER emp_dept_ins
 2:   AFTER INSERT ON emp_dept
 3:   FOR EACH ROW
 4: BEGIN
 5:   --Increment the employee count for the department
 6:   --referenced by the record just inserted.
 7:   UPDATE department
 8:     SET no_of_emps = NVL(no_of_emps,0)+1
 9:    WHERE dept_id = :NEW.dept_id;
10: END;
11: /

                                               continues

Listing 11.6. continued

12: Trigger created.
13:  CREATE OR REPLACE TRIGGER emp_dept_del
14:   AFTER DELETE ON emp_dept
15:   FOR EACH ROW
16: BEGIN
17:   --Decrement the employee count for the department
18:   --referenced by the record just deleted.
19:   UPDATE department
20:     SET no_of_emps = no_of_emps-1
21:    WHERE dept_id = :OLD.dept_id;
22: END;
23: /
24: Trigger created.
25: CREATE OR REPLACE TRIGGER emp_dept_upd
26:   AFTER UPDATE OF dept_id ON emp_dept
27:   FOR EACH ROW
28: BEGIN
29:   --Increment the employee count for the employee's new department
30:   UPDATE department
31:      SET no_of_emps = NVL(no_of_emps,0)+1
32:    WHERE dept_id = :NEW.dept_id;
33:
34:   --Decrement the employee count for the employee's
35:   --previous department.
36:   UPDATE department
37:      SET no_of_emps = no_of_emps - 1
38:    WHERE dept_id = :OLD.dept_id;
39: END;
40: /
41: Trigger created.

ANALYSIS
The DDL, or data definition language, statements shown in Listing 11.6 create three triggers on the emp_dept table: one for inserts, one for updates, and one for deletes. Each trigger is very simple and just increments and/or decrements the employee counter for the department(s) affected by the operation that fired the trigger.

Now that you have created the triggers, one more task remains to be done. The triggers maintain the employee count; they do not initialize it to the correct value. You must initialize the counts yourself, which can be easily done by issuing the SQL statement shown in Listing 11.7.

INPUT/
OUTPUT
Listing 11.7. Initializing the departmental employee counts.

1: UPDATE department
2:   SET no_of_emps = (SELECT COUNT(*)
3:                       FROM emp_dept
4:                      WHERE emp_dept.dept_id = department.dept_id);
5: 3 rows updated.
6: COMMIT;
7: Commit complete.