13:   --On an update, when the old and new emp_id values are the same,
14:   --we do not need to recheck the count.
15:   IF :OLD.emp_id = :NEW.emp_id THEN
16:     RETURN;
17:   ELSE
18:     --if the employee already is at the max, don't allow him to
19:     --have another department.
20:     IF dept_count >= max_depts THEN
21:       RAISE_APPLICATION_ERROR (-20000,
Â'Employees are limited to a max of two departments.');
22: END IF;
23:   END IF;
24: END;
25: /
26: Trigger created.

ANALYSIS
This trigger fires for each new record inserted into the emp_dept table, and it also fires for each updated record in the same table. In either case it checks to see if the employee in question already has been assigned to the maximum of two departments, and rejects any insert or update if that is the case. There is some special logic in lines 15 and 16 to account for the case where a department assignment is changed, but the employee ID is not changed.

The trigger shown in Listing 11.20 will almost work, but it will fail in certain cases. Listing 11.21 shows how the only_two_departments trigger functions when you insert and update data.

INPUT/
OUTPUT
Listing 11.21. Testing the only_two_departments trigger.

 1: INSERT INTO employee
 2:   (emp_id,emp_name) VALUES (401,'Harvey Wallbanger');
 3: 1 row created.
 4: INSERT INTO employee
 5:   (emp_id,emp_name) VALUES (402,'Scarlet Tanninger');
 6: 1 row created.
 7: INSERT INTO department
 8:   (dept_id, dept_name) VALUES (401,'Fermentation');
 9: 1 row created.
10: INSERT INTO department
11:   (dept_id, dept_name) VALUES (402,'Distillation');
12: 1 row created.
13: INSERT INTO department
14:   (dept_id, dept_name) VALUES (403,'Bottling');
15: 1 row created.
16: INSERT INTO emp_dept
17:   (emp_id, dept_id) VALUES (401,401);
18: 1 row created.
19: INSERT INTO emp_dept
20:   (emp_id, dept_id) VALUES (401,402);
21: 1 row created.

                                                      continues

Listing 11.21. continued

22: INSERT INTO emp_dept
23:   (emp_id, dept_id) VALUES (402,402);
24: 1 row created.
25: INSERT INTO emp_dept
26:   (emp_id, dept_id) VALUES (402,403);
27: 1 row created.
28: INSERT INTO emp_dept
29:   (emp_id, dept_id) VALUES (401,403);
30: INSERT INTO emp_dept
31:             *
32: ERROR at line 1:
33: ORA-20000: Employees are limited to a max of two departments.
34: ORA-06512: at "MY_READER.ONLY_TWO_DEPARTMENTS", line 17
35: ORA-04088: error during execution of trigger
Â'MY_READER.ONLY_TWO_DEPARTMENTS'
36: UPDATE emp_dept
37:   SET dept_id = 403
38:  WHERE emp_id = 401 AND dept_id = 402;
39: UPDATE emp_dept
40:        *
41: ERROR at line 1:
42: ORA-04091: table MY_READER.EMP_DEPT is mutating,
Âtrigger/function may not see it
43: ORA-06512: at "MY_READER.ONLY_TWO_DEPARTMENTS", line 6
44: ORA-04088: error during execution of trigger
Â'MY_READER.ONLY_TWO_DEPARTMENTS'

ANALYSIS
The first five inserts in Listing 11.21 just set up some employee and department records for you to experiment with. The next four inserts assign each employee to two departments. So far, so good. However, the tenth INSERT statement (lines 28_29) attempts to assign employee number 401 to a third department. You can see that the trigger caught this and raised an error (lines 30_35), causing the insert to fail. The last statement (lines 36_38) is an update, and it gives rise to the "mutating" error (lines 39_44) because the trigger is querying the table being changed.

As you can see, this trigger will not serve the purpose of limiting each employee to a maximum of two departments. At this point, there are three options you can use to enforce this rule without redesigning the database:

• Enforce the rule in the application code.
• Take a different approach to the problem.
• Use a combination of table-level and row-level triggers, combined with a package, to enforce the rule at the database level.

Enforcing the rule at the application level is possible but requires that the code to enforce the rule be replicated in each program that updates the emp_dept table. Sometimes you can take an entirely different approach to the problem. For example, in this case you could choose not

to allow updates to the emp_dept table at all. Any changes to an employee's department assignment would then be done by first deleting the old record and then inserting a new one. The third, and most complicated, approach is to code the validation in a table-level trigger while using a row-level trigger to build a list of newly inserted or modified records. This solution takes advantage of the fact that variables in an Oracle stored package persist throughout a database session. To implement this approach, you will need to write

• A table-level before trigger on emp_dept to initialize the list of records being inserted/updated.
• A row-level trigger on emp_dept to add the primary key of each new or updated record to the list.
• A table-level after trigger on emp_dept to loop through the list and check to be sure that none of the updates or inserts violates the maximum of two departments per employee.
• A package implementing procedures to add to the list, initialize the list, and retrieve from the list, and which will also contain the list itself.

Why will the approach just described work? It works because a row-level trigger cannot query a mutating table, but a table-level trigger can. By the time the table-level after trigger fires, all the changes have been made and the table is in a consistent state.

Listing 11.22 shows the emp_dept_procs package, which will be part of the solution to enforce the two-department rule. Listing 11.23 shows the DDL to create the triggers that will enforce this rule. Notice that the triggers in Listing 11.23 each call procedures that are part of the emp_dept_procs package, and that emp_dept_procs contains package-level variables that are used to maintain a list of records that have been inserted or modified.

INPUT/
OUTPUT
Listing 11.22. The emp_dept_procs package.

 1: CREATE OR REPLACE package emp_dept_procs AS
 2:   PROCEDURE init_list;
 3:   PROCEDURE add_to_list (emp_id IN emp_dept.emp_id%TYPE
 4:                         ,dept_id IN emp_dept.dept_id%TYPE);
 5:   FUNCTION get_count RETURN NUMBER;
 6:   PROCEDURE get_from_list (to_get IN BINARY_INTEGER
 7:                           ,emp_id OUT emp_dept.emp_id%TYPE
 8:                           ,dept_id OUT emp_dept.dept_id%TYPE);
 9: END emp_dept_procs;
10: /
11: Package created.
12: CREATE OR REPLACE package body emp_dept_procs AS
13:   --These variables persist throughout a session.
14:   listx   BINARY_INTEGER;  --current max index into the list.
15: 

                                            continues