Oracle - Problem with creating a Trigger which check number of employees in department-CodePudding

I need to create a Trigger in oracle. Trigger has to check if the number of employees in department is between 1 and 10. I'm wondering about this for 2 days and still I can not find a solution.

I wrote something like this, but I got an error: "ORA-04091: table is mutating"

CREATE OR REPLACE TRIGGER deptEmp
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
emp_counts NUMBER;
BEGIN
SELECT COUNT(*) INTO emp_counts FROM emp WHERE emp.deptno = :NEW.deptno;
IF emp_counts > 10 OR emp_counts = 0 THEN
raise_application_error(-20000, 'Not correct number of employees in dept'):
END IF;
END;
/

CodePudding user response：

The count for a first employee of any department is 0, so you can't exclude it from being inserted.

But you need the count after inserting , deleting or updating, so you should add a correcting variable, that accounts for that,

And MT0 explained that is better to use COALESCE (:NEw.deptno,:OLD.deptno) in case :New.deptno is NULL, like when you delete the employee

CREATE tABLe emp (deptno int)

CREATE OR REPLACE TRIGGER deptEmp
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
emp_counts int;
correcting_counts int;
BEGIN
  IF INSERTING THEN 
      correcting_counts := 1;
  ELSIF DELETING THEN
    correcting_counts := -1;
  ELSE
    correcting_counts := 0;
  END IF;
    SELECT (COUNT(*)   correcting_counts) INTO emp_counts FROM emp WHERE emp.deptno = COALESCE(:NEW.deptno, :OLD.deptno);
    IF emp_counts > 10 OR  emp_counts <= 0 THEN
       raise_application_error(-20000,'Not correct number of employees in dept');
    END IF;
END;
/

SELECT * FROM USER_ERRORS;

INSERT INTO emp VALUES(1)

1 rows affected

fiddle

CodePudding user response：

You can use a statement level trigger such as

CREATE OR REPLACE TRIGGER deptEmp
BEFORE INSERT OR UPDATE OR DELETE ON emp
DECLARE
  emp_counts_min INT;
  emp_counts_max INT;
BEGIN
  SELECT MIN(COUNT(*)), MAX(COUNT(*))
    INTO emp_counts_min, emp_counts_max
    FROM emp 
   GROUP BY deptno; -- overlaps each dept which is provided next to WHERE within the current case   
   
 IF (emp_counts_min NOT BETWEEN 1 AND 10) OR (emp_counts_max NOT BETWEEN 1 AND 10) THEN
  raise_application_error(-20001, 'Not correct number of employees in dept'):
 END IF;
END;
/

rather than a row-level trigger in order to prevent table mutating error.

CodePudding user response：

You can use:

CREATE OR REPLACE TRIGGER deptEmp
  AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
  v_invalid PLS_INTEGER;
BEGIN
  SELECT 1
  INTO   v_invalid
  FROM   (
    SELECT 1
    FROM   departments d
           LEFT OUTER JOIN emp e
           ON (e.deptno = d.deptno)
    GROUP BY d.deptno
    HAVING COUNT(e.deptno) < 1
    OR     COUNT(e.deptno) > 10
  )
  WHERE  ROWNUM = 1;

  RAISE_APPLICATION_ERROR(
    -20000,
    'Must be between 1 and 10 employees in a department'
  );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

Then, if you have the tables:

CREATE TABLE departments (deptno int PRIMARY KEY);
CREATE TABLE emp (deptno REFERENCES departments (deptno) );

You can create departments and employees:

INSERT INTO departments (deptno) VALUES (1);
INSERT INTO emp (deptno) VALUES (1);
INSERT INTO departments (deptno) VALUES (2);
INSERT INTO emp (deptno) VALUES (2);

If you try to insert too many employees:

INSERT INTO emp (deptno)
SELECT 1 FROM DUAL CONNECT BY LEVEL <= 12;

Then it fails with:

ORA-20000: Must be between 1 and 10 employees in a department

Similarly, it will fail if you try to DELETE or UPDATE and leave it in an invalid state.

fiddle