Trigger to check the employee count per department : Business Logic Trigger « Trigger « Oracle PL / SQL

1. Oracle PL / SQL
2. Trigger
3. Business Logic Trigger

Trigger to check the employee count per department

 
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 "JAVA2S.ONLY_TWO_DEPARTMENTS", line
13
ORA-04088: error during execution of trigger
'JAVA2S.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 JAVA2S.EMP_DEPT is mutating,
trigger/function may not see it
ORA-06512: at "JAVA2S.ONLY_TWO_DEPARTMENTS", line
5
ORA-04088: error during execution of trigger
'JAVA2S.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> --

 

Related examples in the same category