Triggers

 < 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 > 



Oracle to DB2 UDB Conversion Guide2003
Oracle to DB2 UDB Conversion Guide2003
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 132

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net