Data Encryption in Transit - Expert Oracle and Java Security - page 126

THEN
INSERT INTO employees
...
ELSE
UPDATE employees
SET first_name = m_first_name, last_name = m_last_name, email = m_email,
phone_number = m_phone_number, hire_date = m_hire_date,
-- Job History Constraint -- job_id = m_job_id,
salary = v_salary, commission_pct = v_commission_pct,
manager_id = m_manager_id
-- Job History Constraint -- , department_id = m_department_id
WHERE employee_id = m_employee_id;
END IF IF;
Integrity Constraint on Employees Table
You will see in the previous code that we skip updating two of the columns: JOB_ID and DEPARTMENT_ID .
The reason is that there is an existing trigger on the EMPLOYEES table that inserts a record in JOB_HISTORY
when either of those 2 columns in an EMPLOYEES ' record is updated. The trigger code is shown in Listing
7-14.
Listing 7-14. An Existing Integrity Constraint on Employees Table, HR.update_job_history
CREATE OR REPLACE TRIGGER HR.update_job_history
AFTER UPDATE OF job_id, department_id ON HR.EMPLOYEES FOR EACH ROW
BEGIN
add_job_history (:old.employee_id, :old. hire_date, sysdate,
:old.job_id, :old.department_id);
END;
You can see the trigger in Listing 7-14 calls a procedure, add_job_history . All that procedure does is
INSERT a record into the JOB_HISTORY table. However, the JOB_HISTORY table includes a UNIQUE index on
( EMPLOYEE_ID, START_DATE ).
To summarize the problem: if you try to update an EMPLOYEES ' JOB_ID , or DEPARTMENT_ID more than
once a day, it fails because the trigger cannot insert another record in the JOB_HISTORY table for that
same user on the same day. This is a business rule that the developers of the HR sample schema are
enforcing through a UNIQUE index—employees can't change jobs more than once a day.
UPDATE TRIGGER SYNTAX
I'd like to point out one aspect of the trigger syntax. Do you see the :old. prefix in Listing 7-14? That
prefix indicates that we are using the value that already exists in the table. Because this is an AFTER
UPDATE trigger, the value that exists in the table is the same value we submitted in the update. This runs
after the data has been updated.
Often triggers can be used to test, filter, and manipulate data being submitted to a table before it is stored.
For example, if I am updating the last name of an employee, I might say:
update employees set last_name = 'coffin' where employee_id = 700;