CREATE TABLE new_emps AS
SELECT empno,ename,sal,deptno,hiredate
FROM emp

CREATE TABLE new_depts AS
SELECT d.deptno,d.dname,d.loc,
sum(e.sal) tot_dept_sal
FROM emp e, dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,d.dname,loc;

create or replace view emp_details AS
SELECT e.empno,e.ename,e.sal,e.deptno,
d.dname,d.loc
FROM emp e, dept d
WHERE e.deptno=d.deptno


CREATE OR REPLACE TRIGGER new_emp_dept
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_details
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO new_emps
VALUES (:NEW.empno, :NEW.ename,:NEW.sal,
:NEW.deptno,SYSDATE);
UPDATE new_depts
SET tot_dept_sal = tot_dept_sal + :NEW.sal
WHERE deptno = :NEW.deptno;
ELSIF DELETING THEN
DELETE FROM new_emps
WHERE empno = :OLD.empno;
UPDATE new_depts
SET tot_dept_sal = tot_dept_sal - :OLD.sal
WHERE deptno = :OLD.deptno;
ELSIF UPDATING ('sal')
THEN
UPDATE new_emps
SET sal = :NEW.sal
WHERE empno = :OLD.empno;
UPDATE new_depts
SET tot_dept_sal = tot_dept_sal + (:NEW.sal - :OLD.sal)
WHERE deptno = :OLD.deptno;
ELSIF UPDATING ('deptno')
THEN
UPDATE new_emps
SET deptno = :NEW.deptno
WHERE empno = :OLD.empno;
UPDATE new_depts
SET tot_dept_sal = tot_dept_sal - :OLD.sal
WHERE deptno = :OLD.deptno;
UPDATE new_depts
SET tot_dept_sal = tot_dept_sal + :NEW.sal
WHERE deptno = :NEW.deptno;
END IF;
END;
/