OraFAQ Forum: SQL & PL/SQL » Another Trigger Issue

Home » SQL & PL/SQL » SQL & PL/SQL » Another Trigger Issue (Oracle 10g; XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Another Trigger Issue [message #350008]

Tue, 23 September 2008 14:02

ora_newbie
Messages: 19
Registered: August 2008

Junior Member

I have searched the forum and unfortunately I see many solutions on triggers, but not one that may answer my problem. You all helped me before with my first trigger and implementation has been going swimmingly, but that is when all my column data types are of varchar2(). The insert and delete work as expected, but when testing the update the following error is received: ORA-06502: numeric or value error: char to num conversion error... It compiled with no syntax errors, but nevertheless it's not working as expected.

create table statement is as follows:

CREATE TABLE S_ABC 
    (s_ABC_id NUMBER NOT NULL,
    part_id NUMBER NOT NULL, 
    nm_TX VARCHAR2(200) NOT NULL, 
    acrynm_tx VARCHAR2(50), 
    org_id NUMBER,
    ext_dsc_tx CLOB, 
    ioc_dt DATE, 
    CONSTRAINT s_ABC_id_pk PRIMARY KEY (s_ABC_id)) 
TABLESPACE DATATOOLS;

Sequence statement:

CREATE SEQUENCE S_ABC_ID_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;

Trigger statement:

create or replace trigger S_ABC_ID_SEQ_TR 
after Insert or update or delete on ABC 
for each row 

declare
  ABC_chng_cd_v 	s_ABC.ais_chng_cd%type;
  ABC_mod_c_dt_v 	s_ABC.ais_mod_c_dt%type;
  PART_ID_v		ABC.PART_ID%type;
  ABC_chng_dsc_tx_v  	s_ABC.ais_chng_dsc_tx%type;

begin
 if inserting then
        ais_chng_cd_v :='I';
        ais_mod_c_dt_v    :=sysdate;
        ais_chng_dsc_tx_v := '';
  insert into s_ABC	
	Values 
	(S_ABC_ID_SEQ.NEXTVAL,
	:new.PART_ID,
	:new.NM,
	:new.ACRYNM_TX,
	:new.ORG_ID,
	:new.EXT_DSC_TX,
	:new.IOC_DT,
	ABC_chng_cd_v,
	ABC_mod_c_dt_v,
	ABC_chng_dsc_tx_v);
  
elsif updating then
	ais_chng_cd_v  :='U';
        ais_chng_dsc_tx_v := '';	

		if nvl (:old.NM, 'null')!=nvl(:new.NM, 'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'NM, ';
		end if;
		if nvl (:old.ACRYNM_TX, 'null')!=nvl(:new.ACRYNM_TX, 'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'ACRYNM_TX, ';
		end if;
		if nvl (:old.ORG_ID,'null')!=nvl(:new.ORG_ID, 'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'ORG_ID, ';
		end if;		
		if nvl (:old.EXT_DSC_TX,'null')!=nvl(:new.EXT_DSC_TX,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'EXT_DSC_TX, ';
		end if;
		if nvl (:old.IOC_DT,'null')!=nvl(:new.IOC_DT,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'IOC_DT, ';
		end if;
		
	update s_ais set ais_mod_c_dt = sysdate where :new.PART_ID = :old.PART_ID;
  	insert into s_ABC	
	Values 
	(S_ABC_ID_SEQ.NEXTVAL,
	:new.PART_ID,
	:new.NM,
	:new.ACRYNM_TX,
	to_number(:new.ORG_ID),
	to_clob (:new.EXT_DSC_TX),
	to_date (:new.IOC_DT),
	ABC_chng_cd_v,
	ABC_mod_c_dt_v,
	ABC_chng_dsc_tx_v);

elsif deleting then
	ABC_chng_cd_v :='D';
        ABC_mod_c_dt_v    :=sysdate;
        ABC_chng_dsc_tx_v := '';
  	insert into s_ABC	
	Values 
	(S_ABC_ID_SEQ.NEXTVAL,
	:old.PART_ID,
	:old.NM,
	:old.ACRYNM_TX,
	:old.ORG_ID,
	:old.EXT_DSC_TX,
	:old.IOC_DT,
	ABC_chng_cd_v,
	ABC_mod_c_dt_v,
	ABC_chng_dsc_tx_v);

end if;
END;
/

I have researched the error, clobs, numbers, checked the data, and so on. But know, I am just not getting it… any guidance would be greatly appreciated. Thank you