set serveroutput on

CREATE OR REPLACE PACKAGE dla_triggerow AS
  TYPE rek IS
    TABLE OF emp.empno%TYPE
    INDEX BY BINARY_INTEGER;
  zmienione_emp rek;
END dla_triggerow;
/

CREATE OR REPLACE PACKAGE BODY dla_triggerow AS
END dla_triggerow;
/

CREATE OR REPLACE TRIGGER wierszowy
BEFORE UPDATE OF deptno
ON emp
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.Put_Line('wierszowy');
  IF dla_triggerow.zmienione_emp.COUNT = 0 THEN
    dla_triggerow.zmienione_emp(1) := :new.empno;
  ELSE
    dla_triggerow.zmienione_emp(dla_triggerow.zmienione_emp.LAST + 1) := :new.empno;
  END IF;
END;
/

CREATE OR REPLACE TRIGGER tabelowy
AFTER UPDATE OF deptno
ON emp
DECLARE
  x INTEGER;
BEGIN
  DBMS_OUTPUT.Put_Line('tabelowy');
  WHILE dla_triggerow.zmienione_emp.COUNT <> 0 LOOP
    x := dla_triggerow.zmienione_emp(dla_triggerow.zmienione_emp.FIRST);
    UPDATE emp SET sal = (SELECT avg(sal) FROM emp)
    WHERE empno = x;
    dla_triggerow.zmienione_emp.DELETE(dla_triggerow.zmienione_emp.FIRST);
  END LOOP;
END;
/

UPDATE emp SET deptno=deptno;

SELECT ename, sal FROM emp;
ROLLBACK;

DROP TRIGGER wierszowy;
DROP TRIGGER tabelowy;
DROP PACKAGE BODY dla_triggerow;
DROP PACKAGE dla_triggerow;
