| < Day Day Up > |
|
There are seven trigger foreign keys in the ORA_EMP database:
CREATE TRIGGER CreateEmployeeID BEFORE INSERT ON employees FOR EACH ROW BEGIN /* Fill in the emp_id field of employees with the next value from employee_sequence. Since emp_id is a column in employees, :new.emp_id is a valid reference. */ SELECT employee_sequence.nextval INTO :new.emp_id FROM dual; END CreateEmployeeID; / CREATE TRIGGER EmployeesOfficesInsert INSTEAD OF INSERT ON employees_offices DECLARE v_office_ID offices.office_id%TYPE; BEGIN -- First determine the office ID SELECT office_id INTO v_office_ID FROM offices WHERE building = :new.building AND office_id = :new.office_id; -- And now update the account UPDATE employees SET office_id = v_office_ID WHERE dept_code = :new.dept_code AND acct_id = :new.acct_id; END EmployeesOfficesInsert; / CREATE TRIGGER InsertEmployee BEFORE INSERT ON employees FOR EACH ROW DECLARE v_num_projects accounts.num_projects%TYPE; BEGIN SELECT num_projects INTO v_num_projects FROM accounts WHERE dept_code = :new.dept_code AND acct_id = :new.acct_id; :new.current_projects := v_num_projects; UPDATE accounts SET current_employees = current_employees + 1 WHERE dept_code = :new.dept_code AND acct_id = :new.acct_id; END InsertEmployees; / CREATE TRIGGER ManagersChange BEFORE INSERT OR DELETE OR UPDATE ON Employees FOR EACH ROW DECLARE v_ChangeType CHAR(1); BEGIN /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */ IF INSERTING THEN v_ChangeType := 'I'; ELSIF UPDATING THEN v_ChangeType := 'U'; ELSE v_ChangeType := 'D'; END IF; /* Record all the changes made to managers in managers_audit. Use SYSDATE to generate the timestamp, and USER to return the userid of the current user. */ INSERT INTO manager_audit (change_type, changed_by, timestamp, old_employee_id, old_dept_code, old_acct_id, old_band, new_employee_id, new_dept_code, new_acct_id, new_band) VALUES (v_ChangeType, USER, SYSDATE, :old.emp_mgr_id, :old.dept_code, :old.acct_id, :old.band, :new.emp_mgr_id, :new.dept_code, :new.acct_id, :new.band); END ManagersChange; / CREATE TRIGGER office_summary_delete INSTEAD OF DELETE ON office_summary FOR EACH ROW BEGIN -- Delete all of the rows in rooms which match this single row -- in room_summary DELETE FROM offices WHERE building = :old.building; END office_summary_delete; / CREATE TRIGGER UpdateDepartments AFTER INSERT OR DELETE OR UPDATE ON employees DECLARE CURSOR c_projects IS SELECT dept_code ,COUNT(*) AS total_employees ,SUM(current_projects) AS total_projects FROM employees GROUP BY dept_code; BEGIN FOR v_project_rec in c_projects LOOP UPDATE departments SET total_projects = v_project_rec.total_projects ,total_employees = v_project_rec.total_employees WHERE dept_code = v_project_rec.dept_code; END LOOP; END UpdateDepartments; / CREATE TRIGGER UpdateEmployees BEFORE INSERT OR UPDATE ON employees FOR EACH ROW DECLARE v_dept_name departments.dept_name%TYPE; v_dept_code departments.dept_code%TYPE; BEGIN v_dept_code := :NEW.dept_code; SELECT dept_name INTO v_dept_name FROM departments WHERE dept_code = v_dept_code; :NEW.department := v_dept_name; END UpdateEmployees; / ALTER TABLE accounts ADD ( CONSTRAINT fk_acc_dept_code FOREIGN KEY ( dept_code ) REFERENCES departments (dept_code)); CREATE INDEX ind_acct_id ON accounts (acct_id) TABLESPACE users; CREATE INDEX ind_dept_name ON departments (dept_name) TABLESPACE users;
| < Day Day Up > |
|