Oracle Triggers

/*ADDING NEW ROW BY TRIGGER 
/*ORACLE 9i SCOTT/TIGER 
/* TRIGGERED ON EMP TABLE
/*CREATING NEW TABLE WHICH WILL UPDATED BY TRIGGERED */

CREATE TABLE DEPT_SUMMARY
(
DEPTNO NUMBER,
AVG_SALARY NUMBER,
NO_OF_EMP NUMBER
);

create or replace
TRIGGER TG_EMP_SAL_SUMMARY
AFTER INSERT OR DELETE OR UPDATE ON EMP
DECLARE
  CURSOR CUR_EMP
  IS
    SELECT COUNT(ENAME),SUM(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;
    V_CNT NUMBER;
    V_SAL EMP.SAL%TYPE:=0;
    V_DEPT EMP.DEPTNO%TYPE:=0;
BEGIN
  DELETE DEPT_SUMMARY;
  OPEN CUR_EMP;
    LOOP
      FETCH CUR_EMP INTO V_CNT,V_SAL,V_DEPT;
      EXIT WHEN NOT CUR_EMP%FOUND;
      INSERT INTO DEPT_SUMMARY
      VALUES(V_DEPT,V_SAL/V_CNT,V_CNT);
    END LOOP;
END TG_EMP_SAL_SUMMARY;

/*INSERTING NEW ROW INTO EMP*/


INSERT INTO EMP
VALUES(100,'DIBYENDU','ADMIN',15,'12-JAN-00',6580,250,20);

/*OUTPUT ON DEPT_SUMMARY TABLE

DEPTNO	AVG_SALARY	NO_OF_EMP
10	2916.66667	3
20	3204.375	8
30	1560	5

BASED ON (EMP TABLE)

ENAME	SAL	DEPTNO
SMITH	800	20
ALLEN	1600	20
WARD	1250	30
JONES	2975	20
MARTIN	1250	30
BLAKE	2850	30
CLARK	2450	10
SCOTT	3000	20
KING	5000	10
TURNER	1500	30
ADAMS	1100	20
JAMES	950	30
FORD	3000	20
MILLER	1300	10
ENAME	SAL	DEPTNO
DIBYENDU	6580	20
DIBYENDU	6580	20

*/