如何避免“表变异"错误
我有一个触发器，需要在删除一行后从表中读取数据.本质上，我需要计算与当前行相似的剩余行，如果该计数为零，则更新其他地方的字段.
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
希望对你有所帮助.
相关文章