Listing 11.18. continued

 3:   FOR EACH ROW
 4:
 5:
 6: WHEN ((NEW.pay_rate <> OLD.pay_rate)     OR  (NEW.pay_rate
 7: IS NULL AND OLD.pay_rate IS NOT NULL)     OR  (NEW.pay_rate
 8: IS NOT NULL AND OLD.pay_rate IS NULL)     OR  (NEW.pay_type
 9: <> OLD.pay_type)     OR  (NEW.pay_type IS NULL AND
10: OLD.pay_type IS NOT NULL)     OR  (NEW.pay_type IS NOT NULL
11: AND OLD.pay_type IS NULL)      )
12:
13: DECLARE
14:   log_sequence_num    INTEGER;
15: BEGIN
16:   --Get the next value from the sequence. This can only
17:   --be done by using a SELECT statement.
18:   SELECT emp_pay_history_key.NEXTVAL INTO log_sequence_num FROM dual;
19:   --Log this change in the history table
20:   INSERT INTO emp_pay_history
21:     (emp_pay_history_pk, emp_id, as_of, emp_name, pay_type, pay_rate)
22:     VALUES (log_sequence_num
23:            ,NVL(:NEW.emp_id,:OLD.emp_id), SYSDATE
24:            ,NVL(:NEW.emp_name,:OLD.emp_name)
25:            ,:NEW.pay_type, :NEW.pay_rate);
26: END;
27:
28:
29: /
30: Trigger created.

ANALYSIS
The WHEN clause in lines 6_11 won't be formatted exactly as you originally entered it. This is because Oracle stores it in a VARCHAR2 column as one long string. There are also a few extra blank lines in the listing, but the bottom line is that the code can be used to re-create the trigger.

Enabling and Disabling Triggers

Triggers can be temporarily disabled without having to go through the trouble of deleting and then re-creating them. This can be useful if you need to do some special processing such as loading data. The ALTER TRIGGER command is used to enable and disable triggers.

The Syntax for the ALTER TRIGGER Command

ALTER TRIGGER name {ENABLED | DISABLED};

In this syntax, name is the name of the trigger you want to disable or enable.

Consider the only_during_business_hours trigger, which you created and which restricts update operations on the employee table to normal business hours on weekdays. This trigger could cause a problem if you wanted to do payroll processing during the evening. With the ALTER TRIGGER command, you can disable the trigger, run the payroll, and then reenable the trigger again. Listing 11.19 shows an example of this command being used. This listing assumes that the current time is outside the normal business hour range and that the only_during_business_hours trigger is currently enabled.

INPUT/
OUTPUT
Listing 11.19. Disabling a trigger.

 1:  UPDATE employee
 2:   SET pay_rate = 40
 3:  WHERE emp_id = 100;
 4: UPDATE employee
 5:        *
 6: ERROR at line 1:
 7: ORA-20000: Employee changes only allowed during business hours.
 8: ORA-06512: at "MY_READER.ONLY_DURING_BUSINESS_HOURS", line 5
 9: ORA-04088: error during execution of trigger
 `MY_READER.ONLY_DURING_BUSINESS_HOURS'
10: ALTER TRIGGER only_during_business_hours DISABLE;
11: Trigger altered.
12: UPDATE employee
13:   SET pay_rate = 40
14:  WHERE emp_id = 100;
15: 1 row updated.
16: ALTER TRIGGER only_during_business_hours ENABLE;
17: Trigger altered.

ANALYSIS
The first update failed because it was executed after business hours. After disabling the trigger (line 10), it was possible to reexecute the update, this time successfully. The command in line 16 reenables the trigger.

Trigger Limitations

When writing code for triggers, there are a few limitations you have to keep in mind. Here is a list of some things you cannot do with a trigger:

• Query or modify a mutating table
• Execute data definition language statements
• Execute COMMIT, ROLLBACK, or SAVEPOINT statements

NEW TERM
A mutating table is one that is in the process of being changed while the trigger is executing. For example, executing an UPDATE statement on a table makes that table a mutating table for the duration of the UPDATE statement. Any triggers fired as a result of the update are not allowed to query or modify the table being changed.

NEW TERM
Data definition language statements, such as a CREATE TABLE statement, cannot be executed from within a trigger, nor can they be executed from within any function or procedure that is called by a trigger.

Triggers are also not allowed to execute any sort of transaction control statement such as COMMIT or ROLLBACK. If you think about it, this limitation makes a lot of sense. You would quickly lose control of your transactions, and possibly compromise the integrity of your database, if COMMITs and ROLLBACKs were sprinkled throughout various triggers.

Triggers and Mutating Tables

The problem of a trigger needing to query the table that is being changed by the triggering statement is one that sooner or later vexes every trigger writer. Oracle does not allow row triggers to query the table being modified. Doing so gives rise to an error message that looks like this:

ORA-04091: table MY_READER.EMP_DEPT is mutating,
Âtrigger/function may not see it
ORA-06512: at "MY_READER.ONLY_TWO_DEPARTMENTS", line 6
ORA-04088: error during execution of trigger
Â'MY_READER.ONLY_TWO_DEPARTMENTS'

Oracle refers to the table being changed as a mutating table.

Take a closer look at this issue. Suppose that you wanted to limit employees in the sample database to a maximum of two departments. The data model actually supports an infinite number of departments per employee; thus it is necessary to check each time you modify the emp_dept table to be sure that the two-department limit has not been exceeded. To do this, you might first think to write a trigger similar to the one shown in Listing 11.20.

INPUT/
OUTPUT
Listing 11.20. A trigger to enforce the two-department limit.

 1: CREATE OR REPLACE TRIGGER only_two_departments
 2:   BEFORE UPDATE OR INSERT ON emp_dept
 3:   FOR EACH ROW
 4: DECLARE
 5:   dept_count  INTEGER;      --# of depts for this employee
 6:   max_depts   INTEGER := 2; --max number of depts per employee.
 7: BEGIN
 8:   --Get the current number of departments for this employee.
 9:   SELECT COUNT(*) INTO dept_count
10:     FROM emp_dept
11:    WHERE emp_id = :NEW.emp_id;
12: