有2个表错误: 下面触发器语句错误
Create Or Replace Trigger update_TOS_D_PATIENT_PAST
before update On TOS_D_PATIENT_PAST For Each Row
Begin
update tos_d_patient_info_summary t set t.knj_kiorrk = '1'--TOS_SP_PATIENTINFOMNGDATA.FILL_KNJKIORRK(:new.id) where t.patient_id = :new.Patient_id;
End;
如果是1就没错,如果换成后面被红色注释的语句就报错Fill_Knjkiorrk方法:
FUNCTION FILL_KNJKIORRK(V_PATIENTID IN VARCHAR2
) RETURN VARCHAR2 IS
strCondition VARCHAR2(50) := '';
RefCur_KnjKioRrk CursorType;
strTemp VARCHAR2(50) := '';
strKnjkiorrk VARCHAR2(32767):='';
BEGIN
IF V_PATIENTID IS NOT NULL THEN
strCondition := strCondition || 'PATIENT_ID = ''' || V_PATIENTID ||'''';
END IF;
IF strCondition IS NOT NULL THEN
strCondition := ' WHERE ' || strCondition ;
END IF;
OPEN RefCur_KnjKioRrk FOR 'SELECT
PAST_NAME KnjKioRrk
FROM
TOS_D_PATIENT_PAST
' || strCondition ;
LOOP
FETCH RefCur_KnjKioRrk INTO strTemp;
EXIT WHEN RefCur_KnjKioRrk%NOTFOUND;
IF strKnjkiorrk IS NULL THEN
strKnjkiorrk := strTemp;
ELSE
strKnjkiorrk := strKnjkiorrk || '丄'|| strTemp;
END IF;
END LOOP;
RETURN strKnjkiorrk;
END FILL_KNJKIORRK;
麻烦高手看看怎么改啊!!!!!!!!!!!!!追加一个号400分求教
Create Or Replace Trigger update_TOS_D_PATIENT_PAST
before update On TOS_D_PATIENT_PAST For Each Row
Begin
update tos_d_patient_info_summary t set t.knj_kiorrk = '1'--TOS_SP_PATIENTINFOMNGDATA.FILL_KNJKIORRK(:new.id) where t.patient_id = :new.Patient_id;
End;
如果是1就没错,如果换成后面被红色注释的语句就报错Fill_Knjkiorrk方法:
FUNCTION FILL_KNJKIORRK(V_PATIENTID IN VARCHAR2
) RETURN VARCHAR2 IS
strCondition VARCHAR2(50) := '';
RefCur_KnjKioRrk CursorType;
strTemp VARCHAR2(50) := '';
strKnjkiorrk VARCHAR2(32767):='';
BEGIN
IF V_PATIENTID IS NOT NULL THEN
strCondition := strCondition || 'PATIENT_ID = ''' || V_PATIENTID ||'''';
END IF;
IF strCondition IS NOT NULL THEN
strCondition := ' WHERE ' || strCondition ;
END IF;
OPEN RefCur_KnjKioRrk FOR 'SELECT
PAST_NAME KnjKioRrk
FROM
TOS_D_PATIENT_PAST
' || strCondition ;
LOOP
FETCH RefCur_KnjKioRrk INTO strTemp;
EXIT WHEN RefCur_KnjKioRrk%NOTFOUND;
IF strKnjkiorrk IS NULL THEN
strKnjkiorrk := strTemp;
ELSE
strKnjkiorrk := strKnjkiorrk || '丄'|| strTemp;
END IF;
END LOOP;
RETURN strKnjkiorrk;
END FILL_KNJKIORRK;
麻烦高手看看怎么改啊!!!!!!!!!!!!!追加一个号400分求教
create trigger trig1 on TOS_D_PATIENT_PAST before update
as
Begin
update tos_d_patient_info_summary t set t.knj_kiorrk = '2'--TOS_SP_PATIENTINFOMNGDATA.FILL_KNJKIORRK(:new.id)
where t.patient_id = :new.Patient_id and t.seq = :new.seq;
End;提示ORA-04071错误 请问怎么回事啊
是不是因为Fill_Knjkiorrk方法中,涉及到的基本表中有很多相同ID的数据,,,所以更新操作报错啊?麻烦帮我看下,,,如果不用for each row行不行啊?
在你编译没有错的情况下,因为function还是对触发器基表进行了查询操作,执行update基表的时候还是有问题。既然你不能修改function,建议还是把function的逻辑搬到触发器变通处理。
select TOS_SP_PATIENTINFOMNGDATA.FILL_KNJKIORRK(id) from dual就能用
Create Or Replace Trigger update_TOS_D_PATIENT_PAST
before update On TOS_D_PATIENT_PAST For Each Row
Begin
declare rnt varchar2(4000);
begin
select TOS_SP_PATIENTINFOMNGDATA.FILL_KNJKIORRK(:new.PATIENT_ID) into rnt from TOS_D_PATIENT_PAST t where t.patient_id = :new.patient_id;
end;
End;
更新TOS_D_PATIENT_PAST时提示错误:
ORA-04091
0RA-06512
ORA-04088
Create Or Replace Trigger update_TOS_D_PATIENT_PAST
before update On TOS_D_PATIENT_PAST For Each ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;Begin
update tos_d_patient_info_summary t set t.knj_kiorrk =TOS_SP_PATIENTINFOMNGDATA.FILL_KNJKIORRK(:new.id) where t.patient_id = :new.Patient_id;
End;
CREATE TABLE student(no VARCHAR2(5),sname VARCHAR2(10));
INSERT INTO student VALUES ('001','张三');
INSERT INTO student VALUES ('002','李四');
INSERT INTO student VALUES ('003','王五');
INSERT INTO student VALUES ('004','张三');
CREATE OR REPLACE TRIGGER xstudent
AFTER UPDATE ON student FOR EACH ROW
DECLARE
cur_stu sys_refcursor;
temp student.no%TYPE;
v_no VARCHAR2(100);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
OPEN cur_stu FOR 'select no from student where sname='''||:NEW.sname||'''';
LOOP
FETCH cur_stu INTO temp;
EXIT WHEN cur_stu%NOTFOUND;
IF v_no IS NULL THEN
v_no:=temp;
ELSE
v_no:=v_no || '丄'|| temp;
END IF;
END LOOP;
Dbms_Output.put_line('=='||v_no);
END xstudent;output:
39 Update - 1 row(s), executed in 0 sec.
==001丄004
Total execution time 0 sec.