如何避免“表变异"错误 [英] How to avoid "table mutating" errors

问题描述

我有一个触发器，需要在删除一行后从表中读取数据.本质上，我需要计算与当前行相似的剩余行，如果该计数为零，则更新其他地方的字段.

I have an trigger that needs to read from a table after deleting a row. Essentially, I need to count up the remaining rows that are similar to the current row, and if that count is zero, update a field elsewhere.

经过两天的折腾，我一直无法弄清楚如何重组我的思维过程以允许我这样做.下面是一个例子:

After two days of hammering around, I haven't been able to figure out how to restructure my thought process to allow me to do this. Here is an example:

CREATE OR REPLACE TRIGGER Di_PatMustBeWell
AFTER DELETE 
    ON Diagnosis
    FOR EACH ROW
Declare
    --PRAGMA AUTONOMOUS_TRANSACTION;
    NumDiseases Number;
BEGIN
    SELECT NUMDISEASES INTO Numdiseases
    FROM DiagnosisCount
    where Di_Patient = :OLD.Di_Patient;

    IF( NumDiseases !=  1 ) THEN
        UPDATE Patient SET Pat_Sick = 0 WHERE Pat_Person = :OLD.Di_Patient;
    END IF;
END;
/

推荐答案

简短的回答 - 没有触发器，没有变异.

Short answer - no trigger, no mutating.

您可以使用带有 pragma allowed_transaction 的触发器来计算某些患者的剩余诊断，但不推荐这样做.最好创建新的函数或程序来实现您对已删除诊断的逻辑.像这样:

Yow can use the trigger with pragma autonomous_transaction for counting of remaining diagnoses for certain patient, but it's is not recommended way to do this. Better you create new function or procedure to implement your logic on deleted diagnosis. Something like this:

create table Diagnosis as select 456 idDiseases, 123 di_patient from dual;
/
create table diagnosisCount as select 1 numDiseases, 123 di_patient from dual;
/
create table Patient as select 123 Pat_Person, 1 Pat_Sick from dual;
/
drop trigger di_patmustbewell;

create or replace function deleteDiagnosis(idDiseases number) return number is
    rows_ number;
    di_patient number;
    Numdiseases number;
begin
    <<del>> begin 
        delete Diagnosis where IdDiseases = deleteDiagnosis.IdDiseases
        returning Diagnosis.di_patient into deleteDiagnosis.di_patient
        ;
        rows_ := sql%rowcount;
        if rows_ != 1 then raise too_many_rows; end if;
    end del;
    select count(1) into deleteDiagnosis.numDiseases from Diagnosis where Di_Patient = deleteDiagnosis.di_patient;
    if deleteDiagnosis.numdiseases = 0 then <<upd>> begin 
        update Patient set Pat_Sick = 0 where Pat_Person = deleteDiagnosis.di_patient;
        exception when others then 
            dbms_output.put_line('Cannot update Patient di_patient='||di_patient);
            raise;
    end upd; end if;
    return rows_;
end;
/
show errors

declare rows_ number :=  deleteDiagnosis(456);
begin dbms_output.put_line('deleted '||rows_||' rows'); end;
/

deleted 1 rows

select * from Patient;
PAT_PERSON   PAT_SICK
---------- ----------
       123          0

另一种解决方案，如果您更喜欢(或必须)在您的应用程序中使用触发器 - 在触发器主体中声明返回患者诊断计数的内部函数:

An alternative solution, if you prefer (or must) to use a trigger in your application - declare internal function returning count of patient's diagnoses in the trigger body:

create or replace trigger di_patmustbewell
after delete on diagnosis for each row
declare
    numdiseases number;
    function getNumDiagnosis (di_patient number) return number is
        ret number;
        pragma autonomous_transaction;
    begin
        select count(1) into ret from diagnosis where di_patient = getNumDiagnosis.di_patient;
        return ret;
    end getNumDiagnosis;    
begin
    numDiseases := getNumDiagnosis(:old.di_patient);
    if(numdiseases = 0) then
        update patient set pat_sick = 0 where pat_person = :old.di_patient;
    end if;
end;
/
show errors;

Trigger DI_PATMUSTBEWELL compiled

希望对你有所帮助.

这篇关于如何避免“表变异"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！