我有以下触发器
CREATE OR REPLACE TRIGGER L_BIUR_G_LAY
BEFORE INSERT OR UPDATE ON G_LAY
FOR EACH ROW
When (new.g_roo is not null)
DECLARE
x number(1);
stmt varchar(255);
BEGIN
FOR I IN (SELECT DISTINCT G_TAB FROM G_LEG)
LOOP
stmt := 'select distinct g_lind from ' || i.g_tab || ' where g_roo = ' || :new.g_roo;
Execute immediate stmt into x;
IF (x<>0) THEN
RAISE_APPLICATION_ERROR(-2001, 'G_ROO cannot be inserted where G_LIND IS NOT ZERO');
END IF;
END LOOP;
END;
/
当我这样做时
insert into G_LAY (G_OGCS, G_OGC, G_ROO, G_NM, G_TI, G_AB, G_DATE)
(select G_LAY_SEQ.NEXTVAL, 1, G_ROO, G_LGNDIT, G_UNE, 'Pipe Data Long - ' || G_UR, sysdate
from G_DTABLE where G_LIND = 0);
我收到以下错误错误报告:SQL 错误: ORA-01403: 未找到数据ORA-06512:在"L_BIUR_G_LAY"处,第 10 行ORA-04088:执行触发器"G_LAY"时出错01403. 00000 - "未找到数据">
任何帮助将不胜感激。它在我的触发器中声明后尝试了 PRAGMA AUTONOMOUS_TRANSACTION,但它对我没有帮助
问题不在于您的 INSERT 语句。执行触发器中动态构建的 SELECT 语句时出现问题 - 找不到数据,因此引发 ORA-01403。在我看来,最简单的事情就是做一个选择计数(...(:
CREATE OR REPLACE TRIGGER L_BIUR_G_LAY
BEFORE INSERT OR UPDATE ON G_LAY
FOR EACH ROW
When (new.g_rowno is not null)
DECLARE
x number;
stmt varchar(255);
BEGIN
FOR I IN (SELECT DISTINCT G_DISPCNTRLTAB FROM G_LEG) LOOP
stmt := 'select COUNT(*) from ' || i.g_dispcntrltab ||
' where g_rowno = ' || :new.g_rowno ||
' and g_lind <> 0';
Execute immediate stmt into x;
IF x <> 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'G_RWNO cannot be inserted where G_LIND IS NOT ZERO');
END IF;
END LOOP;
END L_BIUR_G_LAY;
我还更改了引发的错误号,因为 -2001 不在可以使用 RAISE_APPLICATION_ERROR 引发的错误范围内(有效范围为 -20000 到 -20999(。
分享和享受。