我创建了一个trigger,当我修改表study_sample上面的office_phone,home_phone,mobile_phone,address1,address2,address3 字段时，把study_sample中原来的phone和address记录到表study_sample_info_chg_wk中，创建trigger时显示成功创建，但是当我update study_sample中的phone和address时，就报错了。
trigger代码如下：
CREATE OR REPLACE TRIGGER tr_stu_sam_chg_wk
after update of office_phone,home_phone,mobile_phone,address1,address2,address3 on study_sample
for each row
declare
num number;
begin
select count(*) into num from study_sample where id_type = :old.id_type and id_no = :old.id_no;
if( num = 0 ) then
INSERT INTO study_sample_info_chg_wk
VALUES (:OLD.id_type, :OLD.id_no,
:OLD.address1
|| CHR (13)
|| :OLD.address2
|| CHR (13)
|| :OLD.address3,
:OLD.office_phone
|| CHR (13)
|| :OLD.home_phone
|| CHR (13)
|| :OLD.mobile_phone);
else
update study_sample_info_chg_wk set old_address = :OLD.address1
|| CHR (13)
|| :OLD.address2
|| CHR (13)
|| :OLD.address3, old_phone = :OLD.office_phone
|| CHR (13)
|| :OLD.home_phone
|| CHR (13)
|| :OLD.mobile_phone where id_type = :old.id_type and id_no = :old.id_no;
end if;
end;
报错信息如下：
ORA-04091: table PRODUCTIONDEV.STUDY_SAMPLE is mutating, trigger/function may not see it
ORA-06512: at "PRODUCTIONDEV.TR_STU_SAM_CHG_WK", line 4
ORA-04088: error during execution of trigger 'PRODUCTIONDEV.TR_STU_SAM_CHG_WK'
|
