Creating a Trigger with cursor inside

   
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );

Table created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, 'Mar', 'Wells','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, 'Man', 'Kyte','Economics', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, 'Pat', 'Poll','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, 'Tim', 'Viper','History', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, 'Barbara', 'Blues','Economics', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, 'David', 'Large','Music', 4);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, 'Rose', 'Bond','Music', 7);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);

1 row created.

SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);

1 row created.

SQL>
SQL> CREATE TABLE major_stats (
  2    major          VARCHAR2(30),
  3    total_credits  NUMBER,
  4    total_lecturer NUMBER);

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER myTrigger
  2    AFTER INSERT OR DELETE OR UPDATE ON lecturer
  3  DECLARE
  4    CURSOR c_Statistics IS
  5      SELECT major, COUNT(*) total_lecturer,
  6             SUM(current_credits) total_credits
  7        FROM lecturer
  8        GROUP BY major;
  9  BEGIN
 10    FOR v_StatsRecord in c_Statistics LOOP
 11      UPDATE major_stats
 12        SET total_credits = v_StatsRecord.total_credits,
 13            total_lecturer = v_StatsRecord.total_lecturer
 14        WHERE major = v_StatsRecord.major;
 15      IF SQL%NOTFOUND THEN
 16        INSERT INTO major_stats (major, total_credits, total_lecturer)
 17          VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,
 18                  v_StatsRecord.total_lecturer);
 19      END IF;
 20    END LOOP;
 21  END myTrigger;
 22  /

Trigger created.

SQL>
SQL> drop table major_stats;

Table dropped.

SQL> drop table lecturer;

Table dropped.

SQL>

Related examples in the same category

1.	create or replace trigger
2.	Oracle's syntax for creating a trigger based on two tables
3.	Trigger on each row
4.	Use Sequence in a trigger
5.	Empty trigger(before insert or update or delete)
6.	Create tigger on wrapper table
7.	This trigger sends messages over a pipe to record inserts into myStudent.
8.	Cascade inserts into myStudent into session and lecturer.
9.	Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
10.	Trigger Which Modifies a Mutating Table
11.	Autonumbering Trigger
12.	Use RAISE_APPLICATION_ERROR in a trigger
13.	Show errors for a trigger
14.	Submit job from a trigger
15.	Use sysdate and user function in a trigger

Creating a Trigger with cursor inside : Create Trigger « Trigger « Oracle PL / SQL

Related examples in the same category