ANALYSIS
NEW TERM
This UPDATE statement contains no WHERE clause, so it modifies all records in the department table. The correlated subquery (a correlated subquery is one that depends on a value in the main query, in this case the dept_id value) in lines 2 through 4 retrieves the total number of employees assigned to each department. This value is then assigned to the no_of_emps field for that department's record.

Now that you have created the necessary triggers and initialized the counters, you might just want to issue some queries to test your code. Listing 11.8 shows some examples of employee records being inserted, updated, and deleted, and also shows the effect on the no_of_emps field for the affected departments.

INPUT/
OUTPUT
Listing 11.8. Testing the employee count triggers.

 1: --Create some departments.
 2: INSERT INTO department (dept_id, dept_name, no_of_emps)
 3:   VALUES (101,'Building Maintenance',0);
 4: 1 row created.
 5: INSERT INTO department (dept_id, dept_name, no_of_emps)
 6:   VALUES (102,'Fleet Repair',0);
 7: 1 row created.
 8: --Insert some employees.
 9: INSERT INTO employee (emp_id, emp_name, pay_rate, pay_type)
10:     VALUES (102,'Herman T Jugglehead',250000,'S');
11: 1 row created.
12: INSERT INTO employee (emp_id, emp_name, pay_rate, pay_type)
13:     VALUES (103,'Albert Foxtrot',23,'H');
14: 1 row created.
15: INSERT INTO employee (emp_id, emp_name, pay_rate, pay_type)
16:     VALUES (104,'Moncton Dequinder',19.95,'S');
17: 1 row created.
18: --Now, assign each employee to a department and then look at the counts.
19: INSERT INTO emp_dept (emp_id, dept_id) VALUES (102,101);
20: 1 row created.
21: INSERT INTO emp_dept (emp_id, dept_id) VALUES (103,101);
22: 1 row created.
23: INSERT INTO emp_dept (emp_id, dept_id) VALUES (104,102);
24: 1 row created.
25: SELECT * FROM department WHERE dept_id in (101,102);
26: DEPT_ID DEPT_NAME                        NO_OF_EMPS
27: ------- -------------------------------- ----------
28:     102 FLEET REPAIR                              1
29:     101 BUILDING MAINTENANCE                      2
30: --Delete one employee's department assignment and look again at the counts.
31: DELETE FROM emp_dept
32:  WHERE emp_id = 103 and dept_id = 101;
33: 1 row deleted.
34: SELECT * FROM department WHERE dept_id in (101,102);
35: DEPT_ID DEPT_NAME                        NO_OF_EMPS
36: ------- -------------------------------- ----------
37:     102 FLEET REPAIR                              1
38:     101 BUILDING MAINTENANCE                      1

                                                   continues

Listing 11.8. continued

39: --Reassign the other employee and take one last look at the counts.
40: UPDATE emp_dept
41:    SET dept_id = 101
42:  WHERE emp_id = 104 and dept_id = 102;
43: 1 row updated.
44: SELECT * FROM department WHERE dept_id in (101,102);
45:  DEPT_ID DEPT_NAME                        NO_OF_EMPS
46: -------- -------------------------------- ----------
47:      102 FLEET REPAIR                              0
48:      101 BUILDING MAINTENANCE                      2
49: COMMIT;
50: Commit complete.

The advantages of this set of triggers are twofold. They give you a central point of control for maintaining the number of employees in a department, and they relieve you from having to program and test this logic several places in your application.

Enforcing Security

You can use triggers to enhance database security and provide you with a finer level of control over database access than can be achieved simply by granting object privileges to users. Restricting access to a table based on the time of day, or the day of the week, would be a good example of this. Looking at the employee table in the sample database, you might consider it to be worthy of some extra security because it contains the pay rate and pay type fields, which directly affect an employee's pay. Because it would be highly unusual to change someone's pay rate outside of normal business hours, you might decide to disallow it entirely. Listing 11.9 shows a trigger that will only allow changes to the employee table to occur during business hours, and then only on weekdays.

INPUT/
OUTPUT
Listing 11.9. A trigger restricting updates.

 1: --This trigger allows changes to employee records
 2: --only on Mondays through Fridays, and only during
 3: --the hours of 8:00am to 5:00pm.
 4: CREATE OR REPLACE TRIGGER only_during_business_hours
 5:   BEFORE INSERT OR UPDATE OR DELETE ON employee
 6: BEGIN
 7:   IF  TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) < 8 --nothing before 8:00am
 8:       OR TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) >= 5
          Â--changes must be made BEFORE 5:00pm
 9: OR TO_CHAR(SYSDATE,'dy') in (`sun','sat') THEN --nothing on weekends
10:     RAISE_APPLICATION_ERROR (-20000,
        Â'Employee changes only allowed during business hours.');
11: END IF;
12: END;
13: /
14: Trigger created.

ANALYSIS
The trigger shown in Listing 11.9 has been created as a table-level trigger. It fires once at the beginning of an insert, update, or delete, not for each row affected by the query. Because this restriction is based only on the time and day, and not on any values in the employee record, it makes sense to implement it at the table level.

Listing 11.10 shows the effects of attempting an update on the employee table outside of normal business hours.

INPUT/
OUTPUT
Listing 11.10. Testing the business hours restriction.

 1: --Establish what time it is.
 2: SELECT TO_CHAR(SYSDATE,'hh24:mm') FROM dual;
 3: TO_CHAR(SYSDATE,'HH24:MM')
 4: --------------------------
 5: 21:06
 6: UPDATE employee SET pay_rate = 500000 WHERE emp_id = 103;
 7: *
 8: ERROR at line 1:
 9: ORA-20000: Employee changes only allowed during business hours.
10: ORA-06512: at "MY_READER.ONLY_DURING_BUSINESS_HOURS", line 5
11: ORA-04088: error during execution of trigger
 `MY_READER.ONLY_DURING_BUSINESS_HOURS'

ANALYSIS
Notice that because the current time was 21:06 (lines 1_5), or 9:06 p.m. in normal time, the trigger rejected the update and an error message was returned (lines 7_11).

When you try this yourself, you might want to adjust the beginning and ending times of your trigger so you don't have to wait until after 5:00 p.m. to see it fire.

Maintaining History

The last example concerning triggers will involve using them to maintain a historical log of changes to a record. It is a common need, when developing a system, to come up with a way to track changes of certain critical data elements over time. One way to do this is to create