Mega Code Archive

 

Categories / Oracle PLSQL / 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 "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> --