Trigger Which Modifies a Mutating Table

   
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>
SQL>
SQL> CREATE OR REPLACE TRIGGER LimitMajors
  2    BEFORE INSERT OR UPDATE OF major ON lecturer
  3    FOR EACH ROW
  4  DECLARE
  5    studentMax CONSTANT NUMBER := 5;
  6    studentCount NUMBER;
  7  BEGIN
  8    SELECT COUNT(*)
  9      INTO studentCount
 10      FROM lecturer
 11      WHERE major = :new.major;
 12
 13    IF studentCount + 1 > studentMax THEN
 14      RAISE_APPLICATION_ERROR(-20000,
 15        'Too many lecturer in major ' || :new.major);
 16    END IF;
 17  END LimitMajors;
 18  /

Trigger 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>
SQL> drop table lecturer;

Table dropped.

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.	Creating a Trigger with cursor inside
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

Trigger Which Modifies a Mutating Table : Create Trigger « Trigger « Oracle PL / SQL

Related examples in the same category