ORA-04091: таблица мутирует, триггер / функция не может ее видеть [дублировать]

Как вы обнаружили, вы не можете выбрать из той же таблицы, что и триггер уровня строки; он вызывает исключение для таблицы.

Чтобы правильно создать эту проверку с помощью триггера, необходимо создать процедуру для получения заданных пользователем блокировок, чтобы проверка была корректно сериализована в многопользовательской среде.

PROCEDURE request_lock
  (p_lockname                     IN     VARCHAR2
  ,p_lockmode                     IN     INTEGER  DEFAULT dbms_lock.x_mode
  ,p_timeout                      IN     INTEGER  DEFAULT 60
  ,p_release_on_commit            IN     BOOLEAN  DEFAULT TRUE
  ,p_expiration_secs              IN     INTEGER  DEFAULT 600)
IS
  -- dbms_lock.allocate_unique issues implicit commit, so place in its own
  -- transaction so it does not affect the caller
  PRAGMA AUTONOMOUS_TRANSACTION;
  l_lockhandle                   VARCHAR2(128);
  l_return                       NUMBER;
BEGIN
  dbms_lock.allocate_unique
    (lockname                       => p_lockname
    ,lockhandle                     => p_lockhandle
    ,expiration_secs                => p_expiration_secs);
  l_return := dbms_lock.request
    (lockhandle                     => l_lockhandle
    ,lockmode                       => p_lockmode
    ,timeout                        => p_timeout
    ,release_on_commit              => p_release_on_commit);
  IF (l_return not in (0,4)) THEN
    raise_application_error(-20001, 'dbms_lock.request Return Value ' || l_return);
  END IF;
  -- Must COMMIT an autonomous transaction
  COMMIT;
END request_lock;

Эта процедура затем может быть использована в составном триггере (при условии, что, по крайней мере, Oracle 11, это нужно будет разделить на отдельные триггеры в более ранних версиях)

CREATE OR REPLACE TRIGGER too_many_employees
  FOR INSERT OR UPDATE ON employee
  COMPOUND TRIGGER

  -- Table to hold identifiers of inserted/updated employee supervisors
  g_superssns sys.odcivarchar2list;

BEFORE STATEMENT 
IS
BEGIN
  -- Reset the internal employee supervisor table
  g_superssns := sys.odcivarchar2list();
END BEFORE STATEMENT; 

AFTER EACH ROW
IS
BEGIN
  -- Store the inserted/updated supervisors of employees
  IF (  (   INSERTING
        AND :new.superssn IS NOT NULL)
     OR (   UPDATING
        AND (  :new.superssn  <> :old.superssn
            OR :new.superssn IS NOT NULL AND :old.superssn IS NULL) ) )
  THEN           
    g_superssns.EXTEND;
    g_superssns(g_superssns.LAST) := :new.superssn;
  END IF;
END AFTER EACH ROW;

AFTER STATEMENT
IS
  CURSOR csr_supervisors
  IS
    SELECT DISTINCT
           sup.column_value superssn
    FROM TABLE(g_superssns) sup
    ORDER BY sup.column_value;
  CURSOR csr_constraint_violations
    (p_superssn employee.superssn%TYPE)
  IS
    SELECT count(*) employees
    FROM employees
    WHERE pch.superssn = p_superssn
    HAVING count(*) > 5;
  r_constraint_violation csr_constraint_violations%ROWTYPE;
BEGIN
  -- Check if for any inserted/updated employee there exists more than
  -- 5 employees for the same supervisor. Serialise the constraint for each
  -- superssn so concurrent transactions do not affect each other
  FOR r_supervisor IN csr_supervisors LOOP
    request_lock('TOO_MANY_EMPLOYEES_' || r_supervisor.superssn);
    OPEN csr_constraint_violations(r_supervisor.superssn);
    FETCH csr_constraint_violations INTO r_constraint_violation;
    IF csr_constraint_violations%FOUND THEN
      CLOSE csr_constraint_violations;
      raise_application_error(-20001, 'Supervisor ' || r_supervisor.superssn || ' now has ' || r_constraint_violation.employees || ' employees');
    ELSE
      CLOSE csr_constraint_violations;
    END IF;
  END LOOP;
END AFTER STATEMENT;

END;

Вы для управления этим ограничением не требуется таблица blocked_manager. Эта информация может быть получена из таблицы employee.

Или в версиях, предшествующих Oracle 11i:

CREATE OR REPLACE PACKAGE employees_trg
AS
  -- Table to hold identifiers of inserted/updated employee supervisors
  g_superssns sys.odcivarchar2list;
END employees_trg;

CREATE OR REPLACE TRIGGER employee_biu
  BEFORE INSERT OR UPDATE ON employee
IS
BEGIN
  -- Reset the internal employee supervisor table
  employees_trg.g_superssns := sys.odcivarchar2list();
END; 

CREATE OR REPLACE TRIGGER employee_aiur
  AFTER INSERT OR UPDATE ON employee
  FOR EACH ROW
IS
BEGIN
  -- Store the inserted/updated supervisors of employees
  IF (  (   INSERTING
        AND :new.superssn IS NOT NULL)
     OR (   UPDATING
        AND (  :new.superssn  <> :old.superssn
            OR :new.superssn IS NOT NULL AND :old.superssn IS NULL) ) )
  THEN           
    employees_trg.g_superssns.EXTEND;
    employees_trg.g_superssns(employees_trg.g_superssns.LAST) := :new.superssn;
  END IF;
END; 

CREATE OR REPLACE TRIGGER employee_aiu
  AFTER INSERT OR UPDATE ON employee
IS
DECLARE
  CURSOR csr_supervisors
  IS
    SELECT DISTINCT
           sup.column_value superssn
    FROM TABLE(employees_trg.g_superssns) sup
    ORDER BY sup.column_value;
  CURSOR csr_constraint_violations
    (p_superssn employee.superssn%TYPE)
  IS
    SELECT count(*) employees
    FROM employees
    WHERE pch.superssn = p_superssn
    HAVING count(*) > 5;
  r_constraint_violation csr_constraint_violations%ROWTYPE;
BEGIN
  -- Check if for any inserted/updated employee there exists more than
  -- 5 employees for the same supervisor. Serialise the constraint for each
  -- superssn so concurrent transactions do not affect each other
  FOR r_supervisor IN csr_supervisors LOOP
    request_lock('TOO_MANY_EMPLOYEES_' || r_supervisor.superssn);
    OPEN csr_constraint_violations(r_supervisor.superssn);
    FETCH csr_constraint_violations INTO r_constraint_violation;
    IF csr_constraint_violations%FOUND THEN
      CLOSE csr_constraint_violations;
      raise_application_error(-20001, 'Supervisor ' || r_supervisor.superssn || ' now has ' || r_constraint_violation.employees || ' employees');
    ELSE
      CLOSE csr_constraint_violations;
    END IF;
  END LOOP;
END;