Error in Trigger [message #350709] |
Fri, 26 September 2008 00:28  |
jyothsna1612
Messages: 68 Registered: June 2008
|
Member |
|
|
Hi,
I'm unable to trace why the error in the following trigger..Could you please give some hint regarding that error:
As the sql formatter is not giving the formatted code so pasting as the same... Regret the inconvenience..
create or replace trigger G_REF_CGSC_DSDY_FSDO_IUDAR
after insert or update or delete
of QPP_CNTRY_ID
on REF_CGSC_T
referencing new as new old as old
for each row
WHEN (OLD.QPP_CNTRY_ID IS NOT NULL OR NEW.QPP_CNTRY_ID IS NOT NULL)
declare
vqpp_cntry_id REF_CGSC_T.QPP_CNTRY_ID%type;
voutdat varchar2(1);
vmodtp varchar2(1);
doconnect number:=0;
dodisconnect number:=0;
BEGIN
if ((inserting) or (updating and :old.QPP_CNTRY_ID IS NOT NULL))then
vqpp_cntry_id:=:new.QPP_CNTRY_ID;
voutdat:='N';
vmodtp:='I';
doconnect:=1;
elsif ((deleting) or ( updating and :NEW.QPP_CNTRY_ID IS NOT NULL)) then
vqpp_cntry_id:=:OLD.QPP_CNTRY_ID;
voutdat:='Y';
vmodtp:='D';
dodisconnect:=1;
end if;
insert all
when (doconnect=1) then
into T_FSDO_D(
DK_SDY,
DK_DAT,
DK_ADT,
MOD_TMST,
OUTDAT,
MODTP)
values(
TMO_QPP_SDO_T.SDY_ID ,
TMO_QPP_SDO_T.DAT_ID ,
0 ,
SYSDATE ,
voutdat ,
vmodtp)
when (dodisconnect =1) then
into T_FSDO_D(
DK_SDY,
DK_DAT,
DK_ADT,
MOD_TMST,
OUTDAT,
MODTP)
values(
TMO_QPP_SDO_T.SDY_ID ,
TMO_QPP_SDO_T.DAT_ID ,
0 ,
SYSDATE ,
voutdat ,
vmodtp)
select TMO_QPP_SDO_T.SDY_ID as DK_SDY,
TMO_QPP_SDO_T.DAT_ID AS DK_DAT,
0 AS DK_ADT,
SYSDATE AS MOD_TMST,
voutdat AS OUTDAT,
vmodtp AS MODTP
from TMO_QPP_SDO_T
where TMO_QPP_SDO_T.SDY_ID in(select sdy_id
from TMO_QPP_SDY_T
where QPP_CNTRY_ID =vqpp_cntry_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('error');
end;
Error:
19/1 PL/SQL: SQL Statement ignored
44/24 PL/SQL: ORA-00904: "TMO_QPP_SDO_T"."DAT_ID": invalid identifier
Thank you.
|
|
|
|