任何部门的第一个雇员的计数为0，因此不能将其从插入中排除。
但是在插入、删除或更新之后，您需要计数，因此您应该添加一个更正变量，
MT0解释说，在:New.deptno为NULL的情况下，最好使用COALESCE (:NEw.deptno,:OLD.deptno)，就像删除雇员时一样
第一个
fiddle

您可以用途：

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;
/

然后，如果您有这些表：

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

可以创建部门和员工：

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

如果尝试插入过多员工：

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

然后，它将失败，并显示：

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

同样地，如果您尝试DELETE或UPDATE，并让它处于无效状态，它也会失败。
小提琴

您可以使用语句级触发器，如

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;
/

而不是行级触发器，以防止表变异错误。