SQL>
SQL> CREATE TABLE employee(
2 emp_id INTEGER,
3 emp_name VARCHAR2(32),
4 supervised_by INTEGER,
5 pay_rate NUMBER(9,2),
6 pay_type CHAR);
Table created.
SQL> CREATE TABLE department
2 (dept_id INTEGER,
3 dept_name VARCHAR2(32));
Table created.
SQL>
SQL> ALTER TABLE department
2 ADD CONSTRAINT pk_dept PRIMARY KEY (dept_id);
Table altered.
SQL>
SQL> CREATE TABLE emp_dept (emp_id INTEGER, dept_id INTEGER, CONSTRAINT unq_1 unique (emp_id, dept_id));
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER only_two_departments
2 BEFORE UPDATE OR INSERT ON emp_dept
3 FOR EACH ROW
4 DECLARE
5 dept_count INTEGER; --# of depts for this employee
6 max_depts INTEGER := 2; --max number of depts per employee.
7 BEGIN
8 SELECT COUNT(*) INTO dept_count
9 FROM emp_dept
10 WHERE emp_id = :NEW.emp_id;
11
12 IF :OLD.emp_id = :NEW.emp_id THEN
13 RETURN;
14 ELSE
15 IF dept_count >= max_depts THEN
16 RAISE_APPLICATION_ERROR (-20000,'Employees are limited to a max of two departments.');
17 END IF;
18 END IF;
19 END;
20 /
Trigger created.
SQL>
SQL> INSERT INTO employee (emp_id,emp_name) VALUES (401,'Harvey Wallbanger');
1 row created.
SQL> INSERT INTO employee (emp_id,emp_name) VALUES (402,'Scarlet Tanninger');
1 row created.
SQL> INSERT INTO department (dept_id, dept_name) VALUES (401,'Fermentation');
1 row created.
SQL> INSERT INTO department (dept_id, dept_name) VALUES (402,'Distillation');
1 row created.
SQL> INSERT INTO department (dept_id, dept_name) VALUES (403,'Bottling');
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,401);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,402);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (402,402);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (402,403);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,403);
INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,403)
*
ERROR at line 1:
ORA-20000: Employees are limited to a max of two
departments.
ORA-06512: at "RNTSOFT.ONLY_TWO_DEPARTMENTS", line
13
ORA-04088: error during execution of trigger
'RNTSOFT.ONLY_TWO_DEPARTMENTS'
SQL> UPDATE emp_dept SET dept_id = 403 WHERE emp_id = 401 AND dept_id = 402;
UPDATE emp_dept SET dept_id = 403 WHERE emp_id = 401 AND dept_id = 402
*
ERROR at line 1:
ORA-04091: table RNTSOFT.EMP_DEPT is mutating,
trigger/function may not see it
ORA-06512: at "RNTSOFT.ONLY_TWO_DEPARTMENTS", line
5
ORA-04088: error during execution of trigger
'RNTSOFT.ONLY_TWO_DEPARTMENTS'
SQL>
SQL> drop table employee cascade constraints;
Table dropped.
SQL> drop table department cascade constraints;
Table dropped.
SQL> drop table emp_dept cascade constraints;
Table dropped.
SQL> --