Home » SQL & PL/SQL » SQL & PL/SQL » Error in Trigger (Oracle, 10g, windows XP)
Error in Trigger [message #351809] Fri, 03 October 2008 05:14 Go to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi
I've written the following trigger but unable to trace why the error: Could you please suggest me how to solve this.

In the trigger all the columns are as per the table structure only..
SQL> create or replace trigger G_CSF_FL_DCSF_FCSS_IUDAR
  2  after insert or update or delete 
  3  of CSF_FL_ERR_CD,CSF_FL_NM,FL_VER_CNT,FL_TP,CSF_FL_STS,SVC_CD,CHK_SUM,RCPT_TMST,CONV_TMST 
  4  on CSF_FL_T
  5  referencing new as new old as old
  6  for each row
  7  declare
  8  vCSF_FL_SND CSF_FL_T.CSF_FL_SND%type:=case when (inserting or updating) then :new.CSF_FL_SND
  9  when deleting then :old.CSF_FL_SND else null end;
 10  vCSF_FL_ID CSF_FL_T.CSF_FL_ID%type:=case when (inserting or updating) then :new.CSF_FL_ID
 11  when deleting then :old.CSF_FL_ID else null end;
 12  vCSF_FL_ERR_CD CSF_FL_T.CSF_FL_ERR_CD%type:=case when (inserting or updating) then :new.CSF_FL_
ERR_CD
 13  when deleting then :old.CSF_FL_ERR_CD else null end;
 14  vCSF_FL_NM CSF_FL_T.CSF_FL_NM%type:=case when (inserting or updating) then :new.CSF_FL_NM
 15  when deleting then :old.CSF_FL_NM else null end;
 16  vFL_VER_CNT CSF_FL_T.FL_VER_CNT%type:=case when (inserting or updating) then :new.FL_VER_CNT
 17  when deleting then :old.FL_VER_CNT else null end;
 18  vFL_TP CSF_FL_T.FL_TP%type:=case when (inserting or updating) then :new.FL_TP
 19  when deleting then :old.FL_TP else null end;
 20  vCSF_FL_STS CSF_FL_T.CSF_FL_STS%type:=case when (inserting or updating) then :new.CSF_FL_STS
 21  when deleting then :old.CSF_FL_STS else null end;
 22  vSVC_CD CSF_FL_T.SVC_CD%type:=case when (inserting or updating) then :new.SVC_CD
 23  when deleting then :old.SVC_CD else null end;
 24  vCHK_SUM CSF_FL_T.CHK_SUM%type:=case when (inserting or updating) then :new.CHK_SUM
 25  when deleting then :old.CHK_SUM else null end;
 26  vRCPT_TMST CSF_FL_T.RCPT_TMST%type:=case when (inserting or updating) then :new.RCPT_TMST
 27  when deleting then :old.RCPT_TMST else null end;
 28  vCONV_TMST CSF_FL_T.CONV_TMST%type:=case when (inserting or updating) then :new.CONV_TMST
 29  when deleting then :old.CONV_TMST else null end;
 30  vFL_SRC CSF_FL_T.FL_SRC%type:=case when (inserting or updating) then :new.FL_SRC
 31  when deleting then :old.FL_SRC else null end; 
 32  voutdat varchar2(1):=case when (inserting or updating) then 'N' when deleting then 'Y' else nul
l end;
 33  vmodtp varchar2(1):=case when inserting then 'I' when updating then 'U' when deleting then 'D' e
lse null end;
 34  BEGIN 
 35  INSERT INTO T_DCSF_D
 36  (CL_ID,
 37  DK_CSF,
 38  RDK_MRC_CSF,
 39  CSF_NM,
 40  CSF_VER_CNT,
 41  CSF_TP_CD,
 42  CSF_TP_NM,
 43  CSF_STS_CD,
 44  CSF_STS_NM,
 45  CSF_SVC_CD,
 46  CSF_SVC_NM,
 47  CSF_CHK_SUM,
 48  TMST_CSF_RCP,
 49  TMST_CSF_CNV,
 50  CSF_FL_SRC_CD,
 51  CSF_FL_SRC_NM,
 52  OUTDAT,
 53  ADT_ID,
 54  MOD_TP)
 55  select 
 56  vCSF_FL_SND as CL_ID,
 57  vCSF_FL_ID as DK_CSF,
 58  vCSF_FL_ERR_CD as RDK_MRC_CSF,
 59  vCSF_FL_NM as CSF_NM,
 60  vFL_VER_CNT as CSF_VER_CNT,
 61  vFL_TP as CSF_TP_CD,
 62  REF_FL_TP_T.FL_TP_NM as CSF_TP_NM,
 63  vCSF_FL_STS as CSF_STS_CD,
 64  vCSF_FL_STS as CSF_STS_NM,
 65  vSVC_CD as CSF_SVC_CD,
 66  (case  vSVC_CD 
 67  when 'MSP' then 'File processed by messaging service'
 68  when 'M2T' then 'File processed by tap creation service'
 69  when 'ICO' then 'File processed by interconnect service'
 70  when 'CNT' then 'Content Traffic'
 71  else null end) as CSF_SVC_NM,
 72  vCHK_SUM as CSF_CHK_SUM,
 73  vRCPT_TMST as TMST_CSF_RCP,
 74  vCONV_TMST as TMST_CSF_CNV,
 75  TCS_CSF_FL_SRC_T.CSF_FL_SRC_CD as CSF_FL_SRC_CD,
 76  TCS_CSF_FL_SRC_T.CSF_FL_SRC_NM as CSF_FL_SRC_NM,
 77  voutdat as OUTDAT,
 78  0 as ADT_ID,
 79  vmodtp as MOD_TP
 80  FROM REF_FL_TP_T,TCS_CSF_FL_SRC_T
 81  where REF_FL_TP_T.FL_TP=vFL_TP and 
 82  TCS_CSF_FL_SRC_T.CSF_FL_SRC_ID=vFL_SRC;
 83  end;
 84  /

Warning: Trigger created with compilation errors.

SQL> sho err
Errors for TRIGGER G_CSF_FL_DCSF_FCSS_IUDAR:

LINE/COL ERROR
LINE/COL ERROR
-------- --------------------------------------------------
29/1     PL/SQL: SQL Statement ignored
29/13    PL/SQL: ORA-00942: table or view does not exist



As the sql formatter shows the errors while formatting.
So pasting the code same
Regret inconvenience.

[Updated on: Fri, 03 October 2008 05:21]

Report message to a moderator

Re: Error in Trigger [message #351811 is a reply to message #351809] Fri, 03 October 2008 05:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
LINE/COL ERROR
-------- --------------------------------------------------
29/1     PL/SQL: SQL Statement ignored
29/13    PL/SQL: ORA-00942: table or view does not exist


One of the tables you're refering to can't be seen from within the trigger.
Possibly you've mis-spelled the name.
Possibly you've not got a direct grant of the privileges required to see it.
Re: Error in Trigger [message #351812 is a reply to message #351809] Fri, 03 October 2008 05:34 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi JRowbottom,

But i checked again whether the table exists or not and the grants are properly given or not...

Everything is fine...

I'm still in finding out where the error is???
Re: Error in Trigger [message #351815 is a reply to message #351812] Fri, 03 October 2008 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you did to check.
Obviously you missed something, trust me Oracle is right.

Regards
Michel
Re: Error in Trigger [message #351820 is a reply to message #351815] Fri, 03 October 2008 06:20 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
these are the tables i used in the trigger and exist in my schema also...


SQL> desc TCS_CSF_FL_SRC_T
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 CUST_ID                                   NOT NULL VARCHAR2(6)
 CSF_STRM_ID                                        NUMBER(38)
 CSF_FL_SRC_ID                             NOT NULL NUMBER(38)
 CSF_FL_SRC_CD                             NOT NULL VARCHAR2(5)
 CSF_FL_SRC_NM                             NOT NULL VARCHAR2(30)
 MATCH_PTRN                                         VARCHAR2(50)
 LAST_FL_ID                                NOT NULL NUMBER(38)
 SEQ_NO_RWN_CNT                            NOT NULL NUMBER(38)
 LAST_SEQ_RWN_DAT                          NOT NULL DATE
 ENTRY_DATE                                NOT NULL DATE
 MODIFY_DATE                               NOT NULL DATE
 MODIFY_USER                                        VARCHAR2(20)
 REC_VERSION                               NOT NULL NUMBER(38)

SQL> desc REF_FL_TP_T
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 FL_TP                                     NOT NULL NUMBER(38)
 FL_TP_NM                                  NOT NULL VARCHAR2(100)
 FL_TP_DEFINE                              NOT NULL VARCHAR2(50)
 FL_TP_OWNER                               NOT NULL VARCHAR2(50)
 FL_CONT                                   NOT NULL VARCHAR2(1)
 NM_CONV                                   NOT NULL VARCHAR2(100)
 NM_CONV_DESC                              NOT NULL VARCHAR2(1000)
 CMNT                                      NOT NULL VARCHAR2(500)
 ENTRY_DATE                                NOT NULL DATE
 MODIFY_DATE                               NOT NULL DATE
 MODIFY_USER                                        VARCHAR2(20)
 REC_VERSION                               NOT NULL NUMBER(38)
 TTT_ID                                             NUMBER

SQL> desc CSF_FL_T
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 CSF_FL_SND                                NOT NULL NUMBER(38)
 CSF_FL_ID                                 NOT NULL NUMBER(38)
 CSF_FL_NM                                 NOT NULL VARCHAR2(60)
 FL_TP                                     NOT NULL NUMBER(38)
 CHK_SUM                                   NOT NULL NUMBER(38)
 FL_VER_CNT                                NOT NULL NUMBER(38)
 SVC_CD                                             VARCHAR2(3)
 FLW_ID                                             NUMBER(38)
 CSF_FL_STS                                NOT NULL VARCHAR2(1)
 CSF_FL_ERR_CD                                      NUMBER(38)
 RCPT_TMST                                 NOT NULL DATE
 CONV_TMST                                          DATE
 REC_PRS                                            NUMBER(38)
 REC_SKP                                            NUMBER(38)
 REC_MAP                                            NUMBER(38)
 CSF_NCHG                                           NUMBER
 CSF_TX                                             NUMBER
 CSF_FL_CUR                                         NUMBER(38)
 CSF_DUR                                            NUMBER(38)
 CSF_VOL_INC                                        NUMBER(38)
 CSF_VOL_OUT                                        NUMBER(38)
 MCH_GAP_MARKER                                     VARCHAR2(1)
 MCH_SRT_KY                                         NUMBER
 MCH_SEQ_RWN_CNT                                    NUMBER
 CONV_DET_AVL                                       VARCHAR2(1)
 ENTRY_DATE                                NOT NULL DATE
 MODIFY_DATE                               NOT NULL DATE
 MODIFY_USER                                        VARCHAR2(20)
 CSF_FL_SRC_ID                             NOT NULL NUMBER(38)
 CSF_TP                                    NOT NULL VARCHAR2(1)
 TTT_ID                                             NUMBER(32)
 FL_SRC                                             NUMBER(38)



Re: Error in Trigger [message #351823 is a reply to message #351820] Fri, 03 October 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 03 October 2008 12:51
Post what you did to check.
Obviously you missed something, trust me Oracle is right.

Regards
Michel


Re: Error in Trigger [message #351861 is a reply to message #351820] Fri, 03 October 2008 08:45 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In your list I don't see the table you're trying to insert into
Previous Topic: excecute a procedure
Next Topic: Procedure executed from where??????
Goto Forum:
  


Current Time: Fri Dec 09 21:09:30 CST 2016

Total time taken to generate the page: 0.08537 seconds