Skip to main content

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


*/
Labels:

Comments

Post a Comment

Popular posts from this blog

Sample : String Reverse In Java

import java.io.*; class test {     public static void main(String args[])     {         DataInputStream in=new DataInputStream(System.in);         try         {             String text;             System.out.println("\nEnter The Text");             text=in.readLine();             System.out.println("\nIn Reverse Order::\n");             for(int i=text.length()-1;i>=0;i--)             {                 System.out.print(text.charAt(i));             }          ...
Post a Comment
Read more

Using GREP in UNIX

How To Use grep Command In Linux / UNIX by  VIVEK GITE  on  AUGUST 2, 2007  ·  147 COMMENTS H ow do I use grep command in Linux? grep command searches the given file for lines containing a match to the given strings or words. By default, grep prints the matching lines. Use grep to search for lines of text that match one or many regular expressions, and outputs only the matching lines. The name, "grep", derives from the command used to perform a similar operation, using the Unix/Linux text editor ed: g/re/p grep command syntax grep 'word' filename grep 'string1 string2' filename cat otherfile | grep 'something' command | grep 'something' Use grep to search file Search /etc/passwd for boo user: $ grep boo /etc/passwd You can force grep to ignore word case i.e match boo, Boo, BOO and all other combination with -i option: $ grep -i "boo" /etc/passwd Use grep recursively You can search recursively i.e. read all files under each ...
Post a Comment
Read more