a table with the date as part of the primary key and then program all the applications to log changes to it. Listing 11.11 shows the DDL to create such a table to track changes to an employee's pay rate over time. Type in the input and create the table as it will be used in the example to follow.

INPUT/
OUTPUT
Listing 11.11. The employee pay history table.

 1: CREATE TABLE emp_pay_history
 2:   (emp_pay_history_pk   INTEGER,
 3:    emp_id     INTEGER,
 4:    as_of      DATE,
 5:    emp_name   VARCHAR2(32),
 6:    pay_type   CHAR(1),
 7:    pay_rate   NUMBER(9,2),
 8:    constraint emp_pay_history_pk
 9:       primary key (emp_pay_history_pk)
10:   );
11: Table created.
12: CREATE sequence emp_pay_history_key
13:   start with 1
14:   increment by 1
15:   nocycle;
16: Sequence created.

ANALYSIS
The table created by the preceding listing resembles the employee table but has two added fields. One additional field, named as_of (line 4), is of type DATE and represents the date and time an employee's pay was changed. The other additional field, named emp_pay_history_pk (line 2), is used as the table's primary key. To ensure uniqueness and to ensure that you can always add to the log, this primary key is populated from an Oracle sequence (lines 12_15). To make querying and reporting easier, the employee's name will also be saved with each record (line 5).

As you might have already guessed, there is a downside to having the applications maintain this table. The code would need to be replicated in several places, resulting in several possible points of failure. Furthermore, if you weren't developing a new system from scratch, you might already have several applications written and in production. Changing these would be costly. The solution? You guessed it—write some triggers.

Listing 11.12 shows a trigger that will maintain a chronological salary history for employees in the sample database.

INPUT/
OUTPUT
Listing 11.12. Trigger to maintain employee pay history.

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

ANALYSIS
This one trigger fires for inserts, updates, and deletes (line 2). The WHEN condition (lines 4_10) ensures that the trigger only fires in response to changes in the pay rate or pay type. It is rather long because either of those fields could be null; remember the three-valued logic issues from Day 4, "Writing PL/SQL Expressions." Notice in lines 22 and 23 that the NVL function is used on the emp_id and emp_name fields, and that if the new versions of these are null, the old versions are used. This is to accommodate deletes, because in a trigger the new values of a deleted record are null. However, even when deleting, the new values for pay rate and pay type are always logged (line 24). The SELECT statement in line 16, against the table dual, is used to grab the next available sequence number for use as a primary key.

Listing 11.13 shows an employee record being inserted, the pay rate being updated, and the employee record then being deleted. The history table is shown before and after so that you can see the effect of the trigger.

INPUT/
OUTPUT
Listing 11.13. Pay rate history example.

 1: SELECT * FROM emp_pay_history;
 2: no rows selected
 3: INSERT INTO employee
 4:   (emp_id, emp_name, pay_rate, pay_type)
 5:   VALUES (301,'Jerome Finkbeiner',2000,'H');
 6: 1 row created.
 7: UPDATE employee
 8:   SET pay_rate = 4000000,
 9:       pay_type = `S'
10:  WHERE emp_id = 301;
11: 1 row updated.
12: DELETE FROM employee
13:   WHERE emp_id = 301;
14: 1 row deleted.
15: COLUMN as_of FORMAT a20
16: COLUMN emp_name FORMAT a20
17: SELECT  emp_pay_history_pk,
18:         emp_id,
19:         TO_CHAR(as_of,'dd-Mon-yyyy hh:mm pm') as_of,
20:         emp_name,
21:         pay_type,
22:         pay_rate
23:   FROM  emp_pay_history;
24: EMP_PAY_HISTORY_PK EMP_ID AS_OF                EMP_NAME           P PAY_RATE
25: ------------------ ------ -------------------- ------------------ - --------
26: 7                  301    18-Jun-1997 06:06 pm Jerome Finkbeiner  H     2000
27: 8                  301    18-Jun-1997 06:06 pm Jerome Finkbeiner  S  4000000
28: 9                  301    18-Jun-1997 06:06 pm Jerome Finkbeiner

ANALYSIS
You can see from lines 1 and 2 that the history table is initially empty. A new employee is then inserted (lines 3_5), his pay rate is changed from hourly to salaried (lines 7_10), and finally the employee is deleted (lines 12_13). The SELECT statement in line 17 displays the history table again, and this time it does have some data. There is one history record for each change made to Jerome Finkbeiner's pay rate. The last history record contains a null rate and type to reflect the fact that the employee record was deleted.

Managing Triggers

If you write database triggers, you need to be able to do several things in terms of managing the code:

• List triggers that already exist
• View code for triggers that already exist
• Enable and disable your triggers

If you are fortunate enough to have one of the new "workbench" type tools, such as Sylvain Faust's SQL-Programmer, these tasks become very easy. For the purposes of this book,