ORA-00984在触发器验证中。
ORA-00984在触发器验证中。
我对pl/sql开发还有点生疏，我创建了这个触发器：
create or replace trigger schema.trg_CP
after insert on tdlrp
referencing old as old
for each row
---------------------------------------------------------------------------------------------------------
declare
v_fkidnc schema.tdlrp.fkidnc%type;
v_errortype schema.tdlrp.xerrort%type;
v_fkerrorID schema.tepm.ferror%type;
v_linerror number;
v_pr schema.tpm.pipm%type;
v_pkdocid_r schema.tddr.pidr%type;
---------------------------------------------------------------------------------------------------------
begin
if inserting then
select fkidnc, xerrort
into v_fkidnc, v_errortype
from schema.tdlrp;
--
if v_fkidnc = 1 then
if v_errortype = 1 then
select ferror, fipcm
into v_fkerrorID, v_linerror
from schema.tepm;
select pipm
into v_pr
from schema.tpm
where fipcm = v_linerror;
insert into schema.tddr(pidr, fipc,user, datea, fiptm)
values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
select pidr
into v_pkdocid_r
from tddr
where fiptm = v_pr;
insert into schema.tere(pidr, ferror, fidre, user, datea, fipcm)
values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END trg_CP;
当我运行脚本时，出现错误：
PL/SQL: ORA-00984: column not allowed in here,
这个错误与select attr into variable有关。
我该如何绕过这个错误？语法是否有误？
编辑 2022年9月15日 15:31
按照建议进行更改后，现在我得到了：
PLS-00382: expression is of wrong type
在begin语句中。
现在我的触发器如下所示：
create or replace trigger schema.trg_CP
after insert on tdlrp
referencing old as old
for each row
---------------------------------------------------------------------------------------------------------
declare
v_fkidnc schema.tdlrp.fkidnc%type;
v_errortype schema.tdlrp.xerrort%type;
v_fkerrorID schema.tepm.ferror%type;
v_linerror number;
v_pr schema.tpm.pipm%type;
v_pkdocid_r schema.tddr.pidr%type;
---------------------------------------------------------------------------------------------------------
begin
select fkidnc, xerrort
into v_fkidnc, v_errortype
from schema.tdlrp;
--
if :new.fkidnc = 1 and :new.errortype = 1 then
select ferror, fipcm
into v_fkerrorID, v_linerror
from schema.tepm;
select pipm
into v_pr
from schema.tpm
where fipcm = v_linerror;
insert into schema.tddr(pidr, fipc,user, datea, fiptm)
values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
select pidr
into v_pkdocid_r
from tddr
where fiptm = v_pr;
insert into schema.tere(pidr, ferror, fidre, user, datea, fipcm)
values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
END IF;
--
EXCEPTION
WHEN OTHERS THEN
RAISE;
END trg_CP;
ORA-00984 inside trigger validations问题出现的原因是在触发器中使用了错误的变量名称以及在插入命令中列名的顺序错误。
解决方法是修改触发器代码，使用正确的变量名称，同时检查插入命令中列名的顺序是否正确。另外，需要注意避免使用Oracle中的保留字作为列名。
以下是已修改的触发器代码：
create or replace trigger schema.trg_CP
after insert on tdlrp
referencing old as old
for each row
---------------------------------------------------------------------------------------------------------
declare
v_fkerrorID schema.tepm.ferror%type;
v_linerror number;
v_pr schema.tpm.pipm%type;
v_pkdocid_r schema.tddr.pidr%type;
---------------------------------------------------------------------------------------------------------
begin
--
if :new.fkidnc = 1 and :new.errortype = 1 then
select ferror, fipcm
into v_fkerrorID, v_linerror
from schema.tepm where --some condition to return 1 row of ferror,fipcm;
select pipm
into v_pr
from schema.tpm
where fipcm = v_linerror;
insert into schema.tddr(pidr, fipc, "user", datea, fiptm)
values(schema.seq_tddr.nextval, :old.fipc, 'A', systimestamp, v_pr);
select pidr
into v_pkdocid_r
from tddr
where fiptm = v_pr;
insert into schema.tere(pidr, ferror, fidre, "user", datea, fipcm)
values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
END IF;
--
EXCEPTION WHEN OTHERS THEN RAISE;
END trg_CP;
请注意，触发器中的`"user"`列名已被修改，以避免使用Oracle中的保留字。此外，插入命令中的列名顺序也已进行了修正。
如果在验证后仍然遇到ORA-00984错误，请检查插入命令中的列名是否正确，并确保插入的值符合要求。