ABD - ficha5

/*a*/
CREATE TABLE D AS SELECT * FROM dept;
ALTER TABLE D add (saltotal NUMBER(7,2));

/*b*/
DECLARE
  v_sal D.saltotal%TYPE;

  CURSOR cl IS
    SELECT deptno FROM D
      FOR UPDATE OF saltotal;

BEGIN
  FOR x IN cl
  LOOP
    SELECT SUM(sal) INTO v_sal FROM Emp WHERE deptno = x.deptno;
    UPDATE D SET saltotal = v_sal WHERE CURRENT OF cl;
  END LOOP;
END;

/*c*/
CREATE OR REPLACE TRIGGER c4
  After DELETE OR INSERT OR UPDATE OF sal,deptno ON Emp
  FOR Each ROW

DECLARE st D.saltotal%TYPE;

BEGIN
  IF inserting THEN
    SELECT saltotal INTO st FROM D WHERE deptno = :NEW.deptno;
    IF st IS NULL THEN
      UPDATE D SET saltotal = :NEW.sal WHERE deptno = :NEW.deptno;
    ELSE
      UPDATE D SET saltotal = st + :NEW.sal WHERE deptno = :NEW.deptno;
    END IF;
  END IF;
  IF deleting THEN
    UPDATE D SET saltotal = saltotal - :old.sal WHERE deptno = :old.deptno;
  END IF;
  IF updating THEN
    IF :NEW.deptno = :old.deptno THEN
      UPDATE D SET saltotal = saltotal - :old.sal + :NEW.sal WHERE deptno = :old.deptno;
    ELSE
      UPDATE D SET saltotal = saltotal - :old.sal WHERE deptno = :old.deptno;
      UPDATE D SET saltotal = saltotal + :NEW.sal WHERE deptno = :NEW.deptno;
    END IF;
  END IF;
END;

UPDATE Emp SET sal=100 WHERE deptno=30;
DELETE FROM Emp WHERE empno = 1876;
INSERT INTO Emp VALUES (1693,'Luis P','Prog',1934,'95.11.30',2000,NULL,10);