PL/SQL触发器获取变异表错误 [英] PL/SQL Trigger gets a mutating table error

问题描述

我的触发器要检查新"经理是否监督不超过5名员工. BLOCKED_MANAGER表(ssn，员工人数)中仅管理5个人的经理. 最后，每个更新都记录在SUPERLOG表中(日期，用户，old_manager，new_manager). 我没有关于触发器的编译错误，但是当我更新superssn时，我得到了这个错误:

My trigger wants to check if a 'new' manager supervises no more than 5 employees. Manager supervising only 5 people are in BLOCKED_MANAGER table(ssn,numberofemployees). Finally, every update is recorded in SUPERLOG table(date,user,old_manager,new_manager). I get no compiling error about the trigger, but when I update a superssn I get this error:

SQL> update employee set superssn='666666607' where ssn='111111100';
update employee set superssn='666666607' where ssn='111111100'
   *
ERROR at line 1:
ORA-04091: Table FRANK.EMPLOYEE is mutating, the trigger/function
can't read it
ORA-06512: a "FRANK.TLOG", line 20
ORA-04088: error during execution of trigger 'FRANK.TLOG'

如何解决此触发条件?谢谢

How can I solve this trigger? Thank you

create or replace trigger tlog 
before update of superssn on employee
for each row
declare
t1 exception;
n number:=0;
cont number:=0;
empl varchar2(16);
cursor cur is (select ssn from blocked_manager where ssn is not null);
begin
open cur;
    loop
fetch cur into empl;
exit when cur%notfound;
if(:new.superssn = empl) then
    n:=1;
end if;
end loop;
close cur;
if n=1 then
raise t1;
end if;
select count(*) into cont from employee group by superssn having superssn=:new.superssn;
if(cont=4) then
insert into blocked_manager values(:new.superssn,5);
end if;
insert into superlog values(sysdate,user,:old.superssn, :new.superssn );
exception
when t1 then
raise_application_error(-20003,'Manager '||:new.superssn||' has already 5 employees');
end;

推荐答案

可能最快的方法是使用精心构造的语句触发器而不是行触发器.行触发器中包含短语FOR EACH ROW，将为每条被修改的行调用(基于触发器的BEFORE/AFTER INSERT，BEFORE/AFTER UPDATE和BEFORE/AFTER DELETE约束)，可以看到相应的:NEW和:OLD值，并且受无法查看定义了触发器的表"规则的约束.语句触发器在每个执行的 statement 的适当时间被调用，看不到行值，但是不受查看定义它们的特定表的限制.因此，对于您不需要使用:NEW或:OLD的逻辑部分，触发这样的触发器可能很有用:

Probably the quickest way around this is to use a carefully constructed statement trigger instead of a row trigger. Row triggers have the phrase FOR EACH ROW in them, are invoked for each row which is modified (based on the BEFORE/AFTER INSERT, BEFORE/AFTER UPDATE, and BEFORE/AFTER DELETE constraints on the trigger), can see the appropriate :NEW and :OLD values, and are subject to the "can't look at the table on which the trigger is defined" rule. Statement triggers are invoked at the appropriate time for each statement which is executed, can't see row values, but aren't subject to the limits on looking at the particular table on which they're defined. So for the portions of your logic which don't need to work with :NEW or :OLD values a trigger such as this might prove useful:

CREATE OR REPLACE TRIGGER EMPLOYEE_S_BU
  BEFORE UPDATE ON EMPLOYEE
  -- Note: no BEFORE EACH ROW phrase, so this is a statement trigger
BEGIN
  -- The following FOR loop should insert rows into BLOCKED_MANAGER for all
  -- supervisors which have four or more employees under them and who are not
  -- already in BLOCKED_MANAGER.

  FOR aRow IN (SELECT e.SUPERSSN, COUNT(e.SUPERSSN) AS EMP_COUNT
                 FROM EMPLOYEE e
                 LEFT OUTER JOIN BLOCKED_MANAGER b
                   ON b.SSN = e.SUPERSSN
                 WHERE b.SSN IS NULL
                 GROUP BY e.SUPERSSN
                 HAVING COUNT(e.SUPERSSN) >= 4)
  LOOP
    INSERT INTO BLOCKED_MANAGER
      (SSN, EMPLOYEE_COUNT)
    VALUES
      (aRow.SUPERSSN, aRow.EMP_COUNT);
  END LOOP;

  -- Remove rows from BLOCKED_MANAGER for managers who supervise fewer
  -- than four employees.

  FOR aRow IN (SELECT e.SUPERSSN, COUNT(e.SUPERSSN) AS EMP_COUNT
                 FROM EMPLOYEE e
                 INNER JOIN BLOCKED_MANAGER b
                   ON b.SSN = e.SUPERSSN
                 GROUP BY e.SUPERSSN
                 HAVING COUNT(e.SUPERSSN) <= 3)
  LOOP
    DELETE FROM BLOCKED_MANAGER
      WHERE SSN = aRow.SUPERSSN;
  END LOOP;      

  -- Finally, if any supervisor has five or more employees under them,
  -- raise an exception. Note that we go directly to EMPLOYEE to determine
  -- the number of employees supervised.

  FOR aRow IN (SELECT SUPERSSN, COUNT(*) AS EMP_COUNT
                 FROM EMPLOYEE
                 GROUP BY SUPERSSN
                 HAVING COUNT(*) >= 5)
  LOOP
    -- If we get here we've found a supervisor with 5 (or more) employees.
    -- Raise an exception

    RAISE_APPLICATION_ERROR(-20000, 'Found supervisor ' || aRow.SUPERSSN ||
                                    ' supervising ' || aRow.EMP_COUNT ||
                                    ' employees');
  END LOOP;
END EMPLOYEE_S_BU;

请注意，如果您摆脱了BLOCKED_MANAGER表(尽管我不知道它是否确实必要，该触发器仍将保留该表)，但逻辑会大大减少.

Note that if you get rid of the BLOCKED_MANAGER table (which this trigger still maintains, although I don't know if it's truly necessary) the logic gets cut down considerably.

您仍然需要一个行触发器来处理日志记录，但是由于这只是减少现有触发器的问题，我将其留给您. :-)

You'll still need a row trigger to handle the logging, but as that's just a matter of cutting down your existing trigger I'll leave that to you. :-)

分享并享受.

这篇关于PL/SQL触发器获取变异表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！