triggers lab part2

triggers lab part2 - 2 3 v_dept Department.code%TYPE 4...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Sheet1 Page 1 9) SQL> exec insertFaculty('999-11-1111', '21-OCT-1961', 'John', 'Smith', 'Emeritus , 'CSE') PL/SQL procedure successfully completed. 10) SQL> select * from faculty PID DOB FIRSTNAME LASTNAME RANK DEP ----------- --------- -------------------- -------------------- ---------- --- 121-11-1111 21-OCT-61 Susan Urban Emeritus CSE 121-22-2222 15-DEC-66 Suzanne Dietrich Associate CSE 121-33-3333 10-APR-45 William Moor Emeritus IEE 121-44-4444 17-MAY-72 Mikhail Barysnikov Full DAN 121-55-5555 17-MAY-42 Richard Smith Emeritus IEE 121-66-6666 20-OCT-76 Michelle Green Assistant MAT 999-11-1111 21-OCT-61 John Smith Emeritus CSE 11) SQL> ALTER TABLE Department ADD totalfaculty NUMBER Table altered. 12) SQL> SELECT dept, count(dept) FROM faculty GROUP BY dept DEP COUNT(DEPT) --- ----------- MAT 1 CSE 3 DAN 1 IEE 2 SQL> CREATE OR REPLACE PROCEDURE gettotalfaculty IS
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 2 3 v_dept Department.code%TYPE 4 v_count Department.totalfaculty%TYPE 5 CURSOR ctotal is 6 SELECT dept,count(dept) FROM faculty GROUP BY dept 7 8 BEGIN 9 OPEN ctotal 10 LOOP 11 UPDATE Department SET totalfaculty = v_count WHERE code = v_dept 12 FETCH ctotal INTO v_dept, v_count 13 Exit when ctotal%NOTFOUND 14 dbms_output.put_line(' Department: '|| v_dept || ' Has ' || v_count || Number of people as Faculty') 15 END LOOP 16 CLOSE ctotal Procedure created. Sheet1 Page 2 13) SQL> update department set totalfaculty = v_count where code = v_dept 14) SQL> CREATE OR REPLACE TRIGGER faculty_after_insert_row 2 AFTER INSERT ON facultyEDB FOR EACH ROW 3 BEGIN 4 UPDATE Department d SET totalfaculty = totalfaculty+1 WHERE d.code = :ne w.dept 5 END 05/06/09 Trigger created....
View Full Document

This note was uploaded on 09/08/2008 for the course CSIS 3310 taught by Professor Guimares during the Spring '08 term at Kennesaw.

Page1 / 2

triggers lab part2 - 2 3 v_dept Department.code%TYPE 4...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online