Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Throw Error Msg (9i)
Trigger Throw Error Msg [message #445895] Thu, 04 March 2010 10:01 Go to next message
kaiserviky
Messages: 6
Registered: March 2010
Junior Member

create table test123 as (unit varchar2(5),qty varchar2(25));

insert into test123('ABC','10,40,50');

insert into test123('PQR','20,30,40,10');

insert into test123('XYZ','20,10,70');



I have a table called test123 which qty field

if the sum of qty is entered more than 100 or less than 100,it should thro error...

I wrote this trigger..but it is not working...Can you pls help me on this

create or replace restrict_sum
after insert or update of qty on test123
	for each row
	declare
	v_sum number;
	v_unit varchar2(15);
	begin
	select x.unit,sum(substr(x.qty,
					INSTR (',' ||x.qty, ',', 1, n),
					INSTR (x.qty|| ',', ',', 1, n) - INSTR ( ',' ||x.qty, ',', 1, n))) into v_unit,v_sum
	from(
		select unit,qty,max(length(qty) - length(replace(qty,',',''))) over (partition by unit) + 1 num from test123) x,
		(select level n from dual connect by level < 1000) y
	where n <= x.num
	group by x.unit;
	
	if ( v_sum <> 100 ) then
		RAISE_APPLICATION_ERROR (-20202,PLS ENTER THE CORRECT VALUE);
	END IF;
	
END;


Thanks a lot
Re: Trigger Throw Error Msg [message #445896 is a reply to message #445895] Thu, 04 March 2010 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I have a table called test123 which qty field
QTY should be datatype NUMBER not VARCHAR2
Re: Trigger Throw Error Msg [message #445897 is a reply to message #445896] Thu, 04 March 2010 10:07 Go to previous messageGo to next message
kaiserviky
Messages: 6
Registered: March 2010
Junior Member
pls find the qty is entered as Comma seperated values

'20,70,10'
Re: Trigger Throw Error Msg [message #445898 is a reply to message #445897] Thu, 04 March 2010 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>pls find the qty is entered as Comma seperated values
Having multiple values in a single column violates Normalization Rules & is POOR design!
Re: Trigger Throw Error Msg [message #445899 is a reply to message #445895] Thu, 04 March 2010 10:18 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your whole design is flawed. As soon as you try to do an update you are going to get a "table is mutating error" as you're not allowed to query the table the trigger is on in row level triggers (unless you're doing an insert values). I wouldn't try and use triggers for this.

And as Blackswan points out you should never store data in comma seperated lists. That just makes your life hard.
Re: Trigger Throw Error Msg [message #445902 is a reply to message #445895] Thu, 04 March 2010 10:47 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To make it work you just have to take the (useful columns of the)query I provided you in your previous topic.
I don't understand why you overcomplexify it here and you have not to select all rows of the table, you just need to check the current given value for qty (previously pct_bonus).

Regards
Michel
Previous Topic: how to use not exists clause in this scenario or do we have any other option
Next Topic: RETURN statement (merged by CM)
Goto Forum:
  


Current Time: Fri Sep 30 02:12:38 CDT 2016

Total time taken to generate the page: 0.17334 seconds