如何避免“表突变"?错误 [英] 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 autonomous_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屋！