触发器如下:
Create Or Replace Trigger Tri_Ab21_Trig
After Update Or Delete Or Insert On Ab21
For Each Row
Declare
Var_Appcode Varchar2(10);
Var_Errormsg Varchar2(2000);
Begin
/*该触发器的功能在于,当ab21发生任何变动时,都调用欠费通知单过程*/
Case
When Inserting Then
--当追加一行时
Pkg_a_Comm.Prc_Qftzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用INSERT AB21:'||Var_Errormsg);
End If;
Pkg_a_Comm.prc_zjtzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用INSERT AB21:'||Var_Errormsg);
End If;
When Updating Then
--修改一行时
Pkg_a_Comm.Prc_Qftzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用UPDATE AB21:'||Var_Errormsg);
End If;
Pkg_a_Comm.prc_zjtzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用UPDATE AB21:'||Var_Errormsg);
End If;
When deleting Then
--删除一行时
Pkg_a_Comm.Prc_Qftzd(:Old.aab001,
:Old.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用DELETE AB21:'||Var_Errormsg);
End If;
Pkg_a_Comm.prc_zjtzd(:Old.aab001,
:Old.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用DELETE AB21:'||Var_Errormsg);
End If;
End Case;
End;
==========================================================
在调用的包之中,有这么一句话:
Select SUM(AAB150),
SUM(AAB151),
SUM(AAB152)
Into VN_AAB131,
VN_AAB132,
VN_AAB133
FROM AB21
WHERE AAB001 = REC_AB07.AAB001
AND AAE002 = REC_AB07.AAE002
AND AAE140 = REC_AB07.AAE140;
=======================================================
执行到这句就报错:ORA-04091: 表 FCSI.AB21 发生了变化,触发器/函数不能读;
第一:我一定要调用这个包;
第二:select sum() from update for update不行;
我该怎么办?
Create Or Replace Trigger Tri_Ab21_Trig
After Update Or Delete Or Insert On Ab21
For Each Row
Declare
Var_Appcode Varchar2(10);
Var_Errormsg Varchar2(2000);
Begin
/*该触发器的功能在于,当ab21发生任何变动时,都调用欠费通知单过程*/
Case
When Inserting Then
--当追加一行时
Pkg_a_Comm.Prc_Qftzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用INSERT AB21:'||Var_Errormsg);
End If;
Pkg_a_Comm.prc_zjtzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用INSERT AB21:'||Var_Errormsg);
End If;
When Updating Then
--修改一行时
Pkg_a_Comm.Prc_Qftzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用UPDATE AB21:'||Var_Errormsg);
End If;
Pkg_a_Comm.prc_zjtzd(:New.aab001,
:New.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用UPDATE AB21:'||Var_Errormsg);
End If;
When deleting Then
--删除一行时
Pkg_a_Comm.Prc_Qftzd(:Old.aab001,
:Old.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用DELETE AB21:'||Var_Errormsg);
End If;
Pkg_a_Comm.prc_zjtzd(:Old.aab001,
:Old.aae002,
Var_Appcode,
Var_Errormsg);
If (To_Number(Var_Appcode) < 0) Then
dbms_output.put_line('触发器调用DELETE AB21:'||Var_Errormsg);
End If;
End Case;
End;
==========================================================
在调用的包之中,有这么一句话:
Select SUM(AAB150),
SUM(AAB151),
SUM(AAB152)
Into VN_AAB131,
VN_AAB132,
VN_AAB133
FROM AB21
WHERE AAB001 = REC_AB07.AAB001
AND AAE002 = REC_AB07.AAE002
AND AAE140 = REC_AB07.AAE140;
=======================================================
执行到这句就报错:ORA-04091: 表 FCSI.AB21 发生了变化,触发器/函数不能读;
第一:我一定要调用这个包;
第二:select sum() from update for update不行;
我该怎么办?
Declare PRAGMA Autonomous_Transaction;
运行中提示
ora-06519:检测到活动的自治事务处理,已经回退;
还是不成功
After Update Or Delete Or Insert On Ab21
For Each Row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
....
commit;
end;