create or replace trigger TR_INTERFACE_H
after insert on ic_interface_h
referencing new as new_value
for each row
declare
-- local variables here
--根据公司编码取主键
cursor c_company is
select pk_corp from bd_corp where unitcode = :new_value.unitcode and dr = 0;
r_company c_company%rowtype; v_company char(4);
begin
open c_company;
fetch c_company into r_company;
if c_company%notfound then
v_company := '@@@@';
else
v_company := r_company.pk_corp;
end if;
close c_company;
end TR_INTERFACE_H;
这是我编写的触发器,在plsql里始终提示编译错误:
Compilation errors for TRIGGER NC50TEST.TR_INTERFACE_HError: PLS-00049: 错误的赋值变量 'NEW_VALUE.UNITCODE'
Line: 9
Text: select pk_corp from bd_corp where unitcode = :new_value.unitcode and dr = 0;
解决方法是你可以把你的cursor c_company 定义成带参数的cursor c_company(v varchar2)is select pk_corp from bd_corp where unitcode = v and dr = 0;
然后在打开的时候 把:new.unitcode 传给游标。open c_company(:new.unitcode);
create table IC_INTERFACE_H
(
cinterfacehid CHAR(20) not null,
unitcode VARCHAR2(40),
busicode VARCHAR2(4),
cbilltypecode VARCHAR2(4),
vbillcode VARCHAR2(30),
dbilldate CHAR(10),
storcode VARCHAR2(40),
fallocflag INTEGER,
deptcode VARCHAR2(40),
cwhsmanagercode VARCHAR2(40),
coperatorcode VARCHAR2(40),
cregister VARCHAR2(40),
daccountdate CHAR(10),
taccounttime CHAR(19),
cauditorcode VARCHAR2(40),
dauditdate CHAR(10),
custcode VARCHAR2(40),
vdiliveraddress VARCHAR2(254),
cdilivertypecode CHAR(20),
cwastewarehousecode VARCHAR2(40),
cbizcode VARCHAR2(40),
cprovidercode VARCHAR2(40),
fbillflag INTEGER,
fspecialflag INTEGER,
freplenishflag CHAR(1),
cothercorpcode CHAR(4),
cotherwarehousecode CHAR(20),
coutcorpcode CHAR(4),
coutwarehousecode CHAR(20),
cotherwhcode VARCHAR2(40),
clastmodiid CHAR(20),
tlastmoditime CHAR(19),
purcorpcode CHAR(4),
tmaketime CHAR(19),
ts CHAR(19),
dr INTEGER
)
after insert on ic_interface_h
referencing new as new_value
for each row
declare
-- local variables here
--根据公司编码取主键
cursor c_company(v varchar2) is
select pk_corp from bd_corp where unitcode = v and dr = 0;
r_company c_company%rowtype; v_company char(4);begin
open c_company(:new_value.unitcode);
fetch c_company into r_company;
if c_company%notfound then
v_company := null;
else
v_company := r_company.pk_corp;
end if;
close c_company;end TR_INTERFACE_H;
错误提示是一样的!!
select 'unitcode' from ic_interface_h
不报错。看来以后要慎用PD了~~
谢谢大家了~~