Home » SQL & PL/SQL » SQL & PL/SQL » Another Trigger Issue (Oracle 10g; XP)
Another Trigger Issue [message #350008] Tue, 23 September 2008 14:02 Go to next message
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
Re: Another Trigger Issue [message #350013 is a reply to message #350008] Tue, 23 September 2008 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
At which line is the error?
Also post the code with line numbers.

Regards
Michel
Re: Another Trigger Issue [message #350026 is a reply to message #350008] Tue, 23 September 2008 15:38 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ora_newbie wrote on Tue, 23 September 2008 15:02

        ais_chng_cd_v :='I';
        ais_mod_c_dt_v    :=sysdate;
        ais_chng_dsc_tx_v := '';



Something is amiss. Your trigger should not compile with those lines in there as they are not declared.

My bet is that :new.ORG_ID has a non-numeric in it.

[Updated on: Tue, 23 September 2008 15:40]

Report message to a moderator

Re: Another Trigger Issue [message #350027 is a reply to message #350026] Tue, 23 September 2008 15:46 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
ais prefix should be ABC... typo. I thought the same and checked, the column data is all numeric for ORG_ID. Thanks!
Re: Another Trigger Issue [message #350028 is a reply to message #350013] Tue, 23 September 2008 15:54 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
I apologize... I was too hasty streamlining the script for discussion and made typos in my haste. I will make corrections and format as requested, thanks!
Re: Another Trigger Issue [message #350031 is a reply to message #350013] Tue, 23 September 2008 16:28 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Again my sincere apologizes for the inconsistencies in naming conventions used.

Base table create statement:
1 CREATE TABLE ABC 
2    (ABC_id NUMBER NOT NULL,
3    part_id NUMBER NOT NULL, 
4    nm_TX VARCHAR2(200) NOT NULL, 
5    acrynm_tx VARCHAR2(50), 
6    org_id NUMBER,
7    ext_dsc_tx CLOB, 
8    ioc_dt DATE, 
9    CONSTRAINT ABC_id_pk PRIMARY KEY (ABC_id)) 
10 TABLESPACE DATATOOLS;


stage table create statement:
1 CREATE TABLE S_ABC 
2    (s_ABC_id NUMBER NOT NULL,
3    part_id NUMBER NOT NULL, 
4    nm_TX VARCHAR2(200) NOT NULL, 
5    acrynm_tx VARCHAR2(50), 
6    org_id NUMBER,
7    ext_dsc_tx CLOB, 
8    ioc_dt DATE, 
9    ABC_chng_cd VARCHAR2(1), 
10   ABC_mod_c_dt DATE,
11   ABC_chng_dsc_tx VARCHAR2(500),  
12   CONSTRAINT s_ABC_id_pk PRIMARY KEY (s_ABC_id)) 
13 TABLESPACE DATATOOLS;


Trigger statement:
1 create or replace trigger S_ABC_ID_SEQ_TR 
2 after Insert or update or delete on ABC 
3 for each row 
4
5  declare
6  ABC_chng_cd_v 	s_ABC.ABC_chng_cd%type;
7  ABC_mod_c_dt_v 	s_ABC.ABC_mod_c_dt%type;
8  PART_ID_v		ABC.PART_ID%type;
9  ABC_chng_dsc_tx_v  	s_ABC.ABC_chng_dsc_tx%type;
10
11 begin
12 if inserting then
13        ABC_chng_cd_v :='I';
14        ABC_mod_c_dt_v    :=sysdate;
15        ABC_chng_dsc_tx_v := '';
16  insert into s_ABC	
17	Values 
18	(S_ABC_ID_SEQ.NEXTVAL,
19	:new.PART_ID,
20	:new.NM_TX,
21	:new.ACRYNM_TX,
22	:new.ORG_ID,
23	:new.EXT_DSC_TX,
24	:new.IOC_DT,
25	ABC_chng_cd_v,
26	ABC_mod_c_dt_v,
27	ABC_chng_dsc_tx_v);
28
29 elsif updating then
30	ABC_chng_cd_v  :='U';
31        ABC_chng_dsc_tx_v := '';	
32
33		if nvl (:old.NM_TX, 'null')!=nvl(:new.NM_TX, 'null') then
34		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'NM, ';
35		end if;
36		if nvl (:old.ACRYNM_TX, 'null')!=nvl(:new.ACRYNM_TX, 'null') then
37 		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ACRYNM_TX, ';
38		end if;
39		if nvl (:old.ORG_ID,'null')!=nvl(:new.ORG_ID, 'null') then
40 		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';
41		end if;		
42		if nvl (:old.EXT_DSC_TX,'null')!=nvl(:new.EXT_DSC_TX,'null') then
43		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'EXT_DSC_TX, ';
44		end if;
45		if nvl (:old.IOC_DT,'null')!=nvl(:new.IOC_DT,'null') then
46		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'IOC_DT, ';
47		end if;
48		
49	update s_abc set abc_mod_c_dt = sysdate where :new.PART_ID = :old.PART_ID;
50  	insert into s_ABC	
51	Values 
52	(S_ABC_ID_SEQ.NEXTVAL,
53	:new.PART_ID,
54	:new.NM_TX,
55	:new.ACRYNM_TX,
56	to_number(:new.ORG_ID),
57	to_clob (:new.EXT_DSC_TX),
58	to_date (:new.IOC_DT),
59	ABC_chng_cd_v,
60	ABC_mod_c_dt_v,
61	ABC_chng_dsc_tx_v);
62
63 elsif deleting then
64	ABC_chng_cd_v :='D';
65      ABC_mod_c_dt_v    :=sysdate;
66      ABC_chng_dsc_tx_v := '';
67  	insert into s_ABC	
68	Values 
69	(S_ABC_ID_SEQ.NEXTVAL,
70	:old.PART_ID,
71	:old.NM_TX,
72	:old.ACRYNM_TX,
73	:old.ORG_ID,
74	:old.EXT_DSC_TX,
75	:old.IOC_DT,
76	ABC_chng_cd_v,
77	ABC_mod_c_dt_v,
78	ABC_chng_dsc_tx_v);
79
80 end if;
81 END;
/


Sample data:
ABC_ID|PART_ID|NM_TX|ACRYNM_TX|ORG_ID|EXT_DSC_TX|IOC_DT
1|4|xxxxx|xxxx||This program develops specifications for the message-level electronic data interchange among systems interacting |
2|7|xdxxxxxx|xxxx|100695||
4|36|Xxxxxxxxxxx xxxx|xxx|367|Xxx is an essential system for the congressionally |10/1/1987

Error Message:
ORA-06502:numeric or value error...
ORA-06512: line 35
ORA-04088: error during execution...

Thank you!
Re: Another Trigger Issue [message #350039 is a reply to message #350031] Tue, 23 September 2008 20:00 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ora_newbie,
ora_newbie wrote on Wed, 24 September 2008 02:58

39		if nvl (:old.ORG_ID,'null')!=nvl(:new.ORG_ID, 'null') then
40 		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';


Your nvl function is trying to assign a string 'null' into a numeric column. You have ORG_ID column as number in your table descripion.

This might be the reason.

Regards,
Jo

[Updated on: Tue, 23 September 2008 20:08]

Report message to a moderator

Re: Another Trigger Issue [message #350211 is a reply to message #350039] Wed, 24 September 2008 07:08 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Your right, I made the following corrections below. The code compiled without syntax errors, however, I receive the same error. This time on line 35. I have continue to research/learn. Thank you for your help.


		if nvl (:old.NM_TX, 'null')!=nvl(:new.NM_TX, 'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'NM, ';
		end if;
		if nvl (:old.ACRYNM_TX, 'null')!=nvl(:new.ACRYNM_TX, 'null') then
 		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ACRYNM_TX, ';
		end if;
		if nvl (to_number(:old.ORG_ID),'null')!=nvl(to_number(:new.ORG_ID), 'null') then
 		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';
		end if;		
		if nvl (to_clob(:old.EXT_DSC_TX),'null')!=nvl (to_clob(:new.EXT_DSC_TX),'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'EXT_DSC_TX, ';
		end if;
		if nvl (to_date(:old.IOC_DT),'null')!=nvl (to_date(:new.IOC_DT),'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'IOC_DT, ';
		end if;
Re: Another Trigger Issue [message #350217 is a reply to message #350008] Wed, 24 September 2008 07:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I have continue to research/learn.

As you use NVL function, you shall read about its behaviour in the documentation. It is placed e.g. on http://tahiti.oracle.com/.
From http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions105.htm#i91798:
Quote:
The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error. The implicit conversion is implemented as follows:
* If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

If the first parameter is numeric, Oracle tries to convert 'null' into number. Unfortunately, this conversion fails.
Re: Another Trigger Issue [message #350221 is a reply to message #350211] Wed, 24 September 2008 07:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ora_newbie wrote on Wed, 24 September 2008 08:08
Your right, I made the following corrections below. The code compiled without syntax errors, however, I receive the same error. This time on line 35.


Same problem with ORG_ID.
Re: Another Trigger Issue [message #350236 is a reply to message #350008] Wed, 24 September 2008 08:22 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
With nulls I've gotten where I like to just be explicit about it...if x1 is not null and x2 is not null and x1 != x2 then...

Also with numeric and value errors, I typically see this as failing a conversion, such as a string into a number, or a varchar overflow.
Re: Another Trigger Issue [message #350388 is a reply to message #350211] Wed, 24 September 2008 22:20 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@oranewbie,

ora_newbie wrote on Wed, 24 September 2008 17:38

	if nvl(to_number(:old.ORG_ID),'null')!=nvl(to_number(:new.ORG_ID), 'null') then
	ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';
	end if;		



to_number converts the parameter supplied to number. But you are still trying to assign the string 'null' to a numeric type. Try executing the following commands and see for yourself what is happening.

    SELECT to_number('null') FROM dual;

    SELECT to_number(null) from dual;

    SELECT to_number('') from dual;

    SELECT nvl(to_number(''), 'null') from dual;

    SELECT nvl(to_number(''), 0) from dual;

Try to replace to_number function with to_char function.

Hope this helps.

Regards,
Jo

[Updated on: Wed, 24 September 2008 22:56]

Report message to a moderator

Re: Another Trigger Issue [message #350888 is a reply to message #350388] Fri, 26 September 2008 19:33 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Thanks all for your help, but I feel that I am not getting it. However, I did stop the conversion error by inserting the below code for org_id and I removed the ' ' around null from all the if statements.

if :old.org_id = :new.org_id then
abc_chng_dsc_tx_v := abc_chng_dsc_tx_v || to_char(:old.org_id) || ', ';
end if


By doing so, it worked as expected for the most part. No conversion error, but fields abc_chng_dsc_tx_v and abc_mod_c_dt did not update.

Now you know why I say, I'm not getting it. Nevertheless, I'll let you all know if lightening strikes. Again, thanks for trying to focus my thoughts. If you care to try again, it is appreciated. At the moment, silly seems fitting as I can not seem to implement what you are all saying.

very respectfully, Gen



Re: Another Trigger Issue [message #350906 is a reply to message #350888] Sat, 27 September 2008 05:31 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ora_newbie,

ora_newbie wrote on Sat, 27 September 2008 06:03

I did stop the conversion error by inserting the below code for org_id and I removed the ' ' around null from all the if statements.


You mean to say your new conditions looks something like this:
	if nvl(to_number(:old.ORG_ID),null)!=nvl(to_number(:new.ORG_ID), null) then
	ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';
	end if;	

If it is like that then the above condition will always fail for a NULL value. Through the above NVL Function, you are trying to assign NULL wherever you find a NULL value. You can't use equality signs with NULLS.

Try to go through This Link to get some idea about NULLS in Oracle.

Anyways, I was suggesting something of this sort:
	if nvl(to_char(:old.ORG_ID),'null')!=nvl(to_char(:new.ORG_ID), 'null') then
	ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';
	end if;	

Can't you post some sort of test case?

I hope this helps.

Regards,
Jo

Re: Another Trigger Issue [message #350907 is a reply to message #350906] Sat, 27 September 2008 06:21 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Good morning Jo-

Thanks for your post. I did try the statement you suggested after reading your post and received the following error, as a result I kept messing around with the code. I should have mentioned that, my bad. The error received is below.

SQL> update cdb.ABC set cdb.ABC.ABC_acrynm_tx = 'ABYSS_II'
  2  where cdb.ABC.part_id = 8888882;
update cdb.ABC set cdb.ABC.ABC_acrynm_tx = 'ABYSS_II'
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDB.S_ABC_ID_SEQ_TR", line 66
ORA-04088: error during execution of trigger 'CDB.S_ABC_ID_SEQ_TR'


I will spend time reviewing your link this morning and get back with you, thanks!

Sorry I will not be able view this link until this evening, I am at work and on a .mil machine, the network shop placed a filter preventing me from viewing it.

[Updated on: Sat, 27 September 2008 06:24]

Report message to a moderator

Re: Another Trigger Issue [message #350941 is a reply to message #350907] Sat, 27 September 2008 23:18 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ora_newbie,

ora_newbie wrote on Sat, 27 September 2008 16:51
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDB.S_ABC_ID_SEQ_TR", line 66
ORA-04088: error during execution of trigger 'CDB.S_ABC_ID_SEQ_TR'




This usually happens when you try to assign a invalid Date value to a Date Column.
The String 'null' is not a valid date.

Change the following code of your trigger:
45     if nvl (:old.IOC_DT,'null')!=nvl(:new.IOC_DT,'null') then
46	     ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'IOC_DT, ';
47     end if;

To
     if nvl (to_char(:old.IOC_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.IOC_DT,'MM/DD/YYYY'),'null') then
	ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'IOC_DT, ';
     end if;


Let me know how it goes.

Regards,
Jo
Re: Another Trigger Issue [message #351140 is a reply to message #350941] Mon, 29 September 2008 08:37 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Oh my, I can be such a goober. I was so focused on the field with a number data type that I never thought to consider the date field as well, a date is numbers. Embarassed Anyway, I updated my script and all appeared to work as expected except for inputting the date. I removed the below update/where statement and all appears to be working now. Do you see any problem removing this line from my statement? On testing changes on the base table, the stage appears to now record them correctly. However, I am not sure if there is an unforeseen impact with my decision.

update s_abc set abc_mod_c_dt = sysdate where :new.PART_ID = :old.PART_ID;


My corrected trigger statement is as follows:

 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.ABC_chng_cd%type;
  ABC_mod_c_dt_v 	s_ABC.ABC_mod_c_dt%type;
  PART_ID_v		ABC.PART_ID%type;
  ABC_chng_dsc_tx_v  	s_ABC.ABC_chng_dsc_tx%type;

 begin
 if inserting then
        ABC_chng_cd_v :='I';
        ABC_mod_c_dt_v    :=sysdate;
        ABC_chng_dsc_tx_v := '';
  insert into s_ABC	
	Values 
	(S_ABC_ID_SEQ.NEXTVAL,
	:new.PART_ID,
	:new.NM_TX,
	: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
	ABC_chng_cd_v  :='U';
        ABC_chng_dsc_tx_v := '';
        ABC_mod_c_dt_v    :=sysdate;
 

		if nvl (:old.NM_TX, 'null')!=nvl(:new.NM_TX, 'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'NM, ';
		end if;
		if nvl (:old.ACRYNM_TX, 'null')!=nvl(:new.ACRYNM_TX, 'null') then
 		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ACRYNM_TX, ';
		end if;
		if nvl(to_char(:old.ORG_ID),'null')!=nvl(to_char(:new.ORG_ID), 'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';
		end if;	
		if nvl (:old.EXT_DSC_TX, 'null')!=nvl (:new.EXT_DSC_TX, 'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'EXT_DSC_TX, ';
		end if;
		if nvl (to_char(:old.IOC_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.IOC_DT,'MM/DD/YYYY'),'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'IOC_DT, ';
		end if;

  	insert into s_ABC	
	Values 
	(S_ABC_ID_SEQ.NEXTVAL,
	:new.PART_ID,
	:new.NM_TX,
	: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 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_TX,
	: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;
/


Thank you, Jo for all your help, time, and patience. As well as everyone else who responded. I picked up two books over the weekend, Oracle DB 10g SQL and Oracle SQL/SQL+ to freshen up on basic SQL. Can you recommend a reference book for programming that you find to be indispensable?

very respectfully, Gen
Re: Another Trigger Issue [message #351190 is a reply to message #351140] Mon, 29 September 2008 14:03 Go to previous message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
I found a problem with removing the update statement and happy to say I figured it out the solution, see below. After I ran a series of test, I finally felt confident to pass it on to our project tester and it passed. I am extremely relieved and no doubt you are all tired of this discussion, neverthless my sincere thanks go out to you all!

elsif updating then
	ABC_chng_cd_v  :='U';
        ABC_chng_dsc_tx_v := '';
 

		if nvl (:old.NM_TX, 'null')!=nvl(:new.NM_TX, 'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'NM, ';
		end if;
		if nvl (:old.ACRYNM_TX, 'null')!=nvl(:new.ACRYNM_TX, 'null') then
 		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ACRYNM_TX, ';
		end if;
		if nvl(to_char(:old.ORG_ID),'null')!=nvl(to_char(:new.ORG_ID), 'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'ORG_ID, ';
		end if;	
		if nvl (:old.EXT_DSC_TX, 'null')!=nvl (:new.EXT_DSC_TX, 'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'EXT_DSC_TX, ';
		end if;
		if nvl (to_char(:old.IOC_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.IOC_DT,'MM/DD/YYYY'),'null') then
		ABC_chng_dsc_tx_v := ABC_chng_dsc_tx_v || 'IOC_DT, ';
		end if;

	update s_abc set abc_mod_c_dt = sysdate where :new.PART_ID = :old.PART_ID and S_ABC_ID <> S_ABC_ID;
  	insert into s_ABC	
	Values 
	(S_ABC_ID_SEQ.NEXTVAL,
	:new.PART_ID,
	:new.NM_TX,
	:new.ACRYNM_TX,
	:new.ORG_ID,
	:new.EXT_DSC_TX,
	:new.IOC_DT,
	ABC_chng_cd_v,
	sysdate,
	ABC_chng_dsc_tx_v);

[Updated on: Mon, 29 September 2008 14:04]

Report message to a moderator

Previous Topic: MIN fUNCTION WITH GROUP BY
Next Topic: ORA-00937: not a single-group group function
Goto Forum:
  


Current Time: Sun Dec 04 00:45:36 CST 2016

Total time taken to generate the page: 0.09325 seconds