Listing 11.22. continued

16:   --Declare a record containing the table's primary key.
17:   TYPE emp_dept_pk IS RECORD (
18:     emp_id  emp_dept.emp_id%TYPE,
19:     dept_id emp_dept.dept_id%TYPE);
20:
21:   --This defines a pl/sql table which will store a list of all records
22:   --"touched" by an insert or update statement.
23:   TYPE emp_dept_list_type IS TABLE OF emp_dept_pk
24:     INDEX BY BINARY_INTEGER;
25:
26:   --Declare the actual table which will contain our list.
27:   emp_dept_list   emp_dept_list_type;
28:
29: PROCEDURE init_list is
30: BEGIN
31:   --Initialize the list pointer to zero.
32:   listx := 0;
33: END;
34:
35: PROCEDURE add_to_list (emp_id IN emp_dept.emp_id%TYPE
36:                       ,dept_id IN emp_dept.dept_id%TYPE) IS
37: BEGIN
38:   --increment the list index and save the primary key values.
39:   listx := listx + 1;
40:   emp_dept_list(listx).emp_id := emp_id;
41:   emp_dept_list(listx).dept_id := dept_id;
42: END;
43:
44: FUNCTION get_count RETURN NUMBER IS
45: BEGIN
46:   --return the number of entries in the list.
47:   RETURN listx;
48: END;
49:
50: PROCEDURE get_from_list (to_get IN BINARY_INTEGER
51:                         ,emp_id OUT emp_dept.emp_id%TYPE
52:                         ,dept_id OUT emp_dept.dept_id%TYPE) IS
53: BEGIN
54:   emp_id := emp_dept_list(to_get).emp_id;
55:   dept_id := emp_dept_list(to_get).dept_id;
56: END;
57:
58: END emp_dept_procs;
59: /
60: Package body created.

ANALYSIS
Lines 1_10 create the package header, which defines the procedures and functions from this package that are available to external objects, such as a trigger. Lines
17_19 define a record containing the primary key fields for the emp_dept table. This record in turn is used to define a PL/SQL table type (lines 23_24). Finally, in line 27, a PL/SQL table is declared. This is the table that will hold the list of records inserted or updated and that must be validated by the table-level after trigger.

INPUT/
OUTPUT
Listing 11.23. Triggers to enforce the two-department limit.

 1: DROP TRIGGER only_two_departments;
 2: Trigger dropped.
 3: CREATE OR REPLACE TRIGGER only_two_departments_1
 4:   BEFORE UPDATE OR INSERT ON emp_dept
 5: BEGIN
 6:   --Reset the list counter before starting any insert/update.
 7:   emp_dept_procs.init_list;
 8: END;
 9: /
10: Trigger created.
11: CREATE OR REPLACE TRIGGER only_two_departments_2
12:   BEFORE UPDATE OR INSERT ON emp_dept
13:   FOR EACH ROW
14: BEGIN
15:   --Add this record to the list of those changed.
16:   --Validation is done after the STATEMENT is finished.
17:   emp_dept_procs.add_to_list(:NEW.emp_id, :NEW.dept_id);
18: END;
19: /
20: Trigger created.
21: CREATE OR REPLACE TRIGGER only_two_departments_3
22:   AFTER UPDATE OR INSERT ON emp_dept
23: DECLARE
24:   check_emp_id    emp_dept.emp_id%TYPE;
25:   check_dept_id   emp_dept.dept_id%TYPE;
26:
27:   listx     BINARY_INTEGER;
28:   list_max  BINARY_INTEGER;
29:
30:   dept_count  NUMBER;
31: BEGIN
32:   --Get the number of records we "touched".
33:   list_max := emp_dept_procs.get_count;
34:
35:   --We need to check each record to see if we have
36:   --violated the "only two departments" rule.
37:   FOR listx IN 1..list_max loop
38:     --Get the primary key for the record we are checking.
39:     emp_dept_procs.get_from_list (listx, check_emp_id, check_dept_id);
40:
41:     --Get the number of departments for this employee.
42:     SELECT COUNT(*) INTO dept_count
43:       FROM emp_dept
44:      WHERE emp_id = check_emp_id;
45:
46:     --Does the employee in question have more than two departments?
47:     IF dept_count > 2 THEN
48:       RAISE_APPLICATION_ERROR(-20000,
          Â'Employees are limited to a max of two departments.');
49: END IF;
50:   END LOOP;
51: END;
52: /
53: Trigger created.

ANALYSIS
Notice in line 1 that the previous trigger is dropped. Be sure to do this. The table-level before trigger in lines 3_9 is fired at the beginning of an INSERT or UPDATE statement. It calls a package procedure that initializes the list counter. The row-level trigger, named only_two_departments_2 (defined in lines 11_19), is fired for each row added or changed. This trigger adds the primary key of each record to the list maintained in the package-level PL/SQL table. The third trigger, defined in lines 21_52, is the one that does the actual validation work. It is fired after the INSERT or UPDATE statement is complete. It loops through each new or changed record and checks to be sure that each employee in question has a maximum of two department assignments.

Now that you have created these triggers and the emp_dept_procs package, you can execute the SQL statements shown in Listing 11.24 in order to demonstrate that it works.

INPUT/
OUTPUT
Listing 11.24. Testing the triggers and package that enforce the two-department rule.

 1: INSERT INTO employee
 2:   (emp_id,emp_name) VALUES (403,'Freddie Fisher');
 3: 1 row created.
 4: INSERT INTO employee
 5:   (emp_id,emp_name) VALUES (404,'Charlie Tuna');
 6: 1 row created.
 7: INSERT INTO department
 8:   (dept_id, dept_name) VALUES (404,'Scale Processing');
 9: 1 row created.
10: INSERT INTO department
11:   (dept_id, dept_name) VALUES (405,'Gutting');
12: 1 row created.
13: INSERT INTO department
14:   (dept_id, dept_name) VALUES (406,'Unloading');
15: 1 row created.
16: INSERT INTO emp_dept
17:   (emp_id, dept_id) VALUES (403,404);
18: 1 row created.
19: INSERT INTO emp_dept
20:   (emp_id, dept_id) VALUES (403,405);
21: 1 row created.
22: INSERT INTO emp_dept
23:   (emp_id, dept_id) VALUES (404,405);
24: 1 row created.
25: INSERT INTO emp_dept
26:   (emp_id, dept_id) VALUES (404,406);
27: 1 row created.
28: INSERT INTO emp_dept
29:   (emp_id, dept_id) VALUES (403,406);
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_3", line 21
35: ORA-04088: error during execution of trigger
Â'MY_READER.ONLY_TWO_DEPARTMENTS_3'

36: UPDATE emp_dept
37:   SET dept_id = 406
38:  WHERE emp_id = 403 AND dept_id = 405;
39: 1 row updated.
40: UPDATE emp_dept
41:    SET emp_id = 403
42:  WHERE emp_id = 404
43:    AND dept_id = 405;
44: UPDATE emp_dept
45:        *
46: ERROR at line 1:
47: ORA-20000: Employees are limited to a max of two departments.
48: ORA-06512: at "MY_READER.ONLY_TWO_DEPARTMENTS_3", line 21
49: ORA-04088: error during execution of trigger
Â`MY_READER.ONLY_TWO_DEPARTMENTS_3'

ANALYSIS
The first five inserts (lines 1_15) put some sample employees and departments in place for testing purposes. The next four inserts (lines 16_27) assign each of the two employees just inserted to two departments. The tenth insert (lines 28_29) attempts to assign employee number 403 to a third department. This violates the two-department rule, causing the insert to fail (lines 30_35). There are two UPDATE statements. The first update (lines 36_38) is allowed because it only changes a department assignment for employee number 403. That employee still has exactly two departments. The second update (lines 40_43) fails because it is changing the emp_id field in a record from 404 to 403, resulting in 403 having more than two department assignments.

Summary

This chapter has been complex, but it gave you the chance to see and experiment with triggers implementing several different types of functionality. To reiterate, some possible uses for triggers are to enforce business rules (Listing 11.3), generate column values (Listing 11.6), enhance security (Listing 11.9), and maintain a historical record (Listing 11.12). These are just the tip of the iceberg. The possibilities are limited only by your creativity and imagination. You have also learned about the mutating table error, the bane of many trigger writers, and should now have a good understanding of how to work around it.