新建 文本文档.txt

delphi renyuanguanlixinxioxitong

CREATE OR REPLACE VIEW dept_emp AS
  SELECT a.departmentno,a.departmentname,b.employeeno,b.employeename
  FROM department a,employee b
  WHERE a.departmentno=b.departmentno
/
CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp 
INSTEAD OF INSERT ON dept_emp FOR EACH ROW
DECLARE
  v_temp INT;
BEGIN
  SELECT count(*) INTO v_temp FROM department 
  WHERE departmentno=:new.departmentno;
  IF v_temp=0 THEN
    INSERT INTO department(departmentno,departmentname) 
    VALUES(:new.departmentno,:new.departmentname);
  END IF;
  SELECT count(*) INTO v_temp FROM employee 
  WHERE employeeno=:new.employeeno;
  IF v_temp=0 THEN
    INSERT INTO employee(employeeno,employeename,departmentno) 
    values(:new.employeeno,:new.employeename,:new.departmentno);
  END f;
end tr_instead_of_dept_emp;