| trigger to compare qty [message #49736] |
Wed, 06 February 2002 23:55  |
hnetscape
Messages: 30
Registered: December 2001
Location: India
|
Member |
|
|
Hi,
I have 2 tables. In the 1st table orderqty will be there (eg.10 nos) and in 2nd table scheduleqty will be there (eg.2,4,3,1) that is in 2nd table sum of scheduleqty will be equals to 1st table orderqty. Now I want a trigger when ever both of this values are not equal then the trigger should fire.
I have tried like this, but it is giving errors.
CREATE or replace TRIGGER raju.ordqty_schqty
BEFORE INSERT OR UPDATE or delete
ON raju.testao2
DECLARE
vordqty testao1.orderqty%type;
vschqty testao2.deliveryqty%type;
BEGIN
select orderqty into vordqty from saao1
where compcode=:new.compcode
and branchcd = :new.branchcd
and seriescd = :new.seriescd
and aono = :new.aono
and slno = :new.slno;
select sum(deliveryqty) into vschqty from saao2
where compcode=:new.compcode
and branchcd = :new.branchcd
and seriescd = :new.seriescd
and aono = :new.aono
and slno = :new.slno;
IF vordqty <> vschqty
THEN raise_application_error( -20501, 'Order Qty and Schedule Qty Does Not Match');
END IF;
END;
/
Can you suggest.
Regards,
Raju
|
|
|
|
| Re: trigger to compare qty [message #49747 is a reply to message #49736] |
Thu, 07 February 2002 09:09  |
Suresh Vemulapalli
Messages: 624
Registered: August 2000
|
Senior Member |
|
|
use row level trigger. you can't reference :new and :old values from statement level trigger.
CREATE or replace TRIGGER raju.ordqty_schqty
BEFORE INSERT OR UPDATE or delete
ON raju.testao2
for each row
DECLARE
vordqty testao1.orderqty%type;
vschqty testao2.deliveryqty%type;
BEGIN
select orderqty into vordqty from saao1
where compcode=:new.compcode
and branchcd = :new.branchcd
and seriescd = :new.seriescd
and aono = :new.aono
and slno = :new.slno;
select sum(deliveryqty) into vschqty from saao2
where compcode=:new.compcode
and branchcd = :new.branchcd
and seriescd = :new.seriescd
and aono = :new.aono
and slno = :new.slno;
IF vordqty <> vschqty
THEN raise_application_error( -20501, 'Order Qty and Schedule Qty Does Not Match');
END IF;
END;
|
|
|
|
| Re: trigger to compare qty [message #49840 is a reply to message #49736] |
Tue, 12 February 2002 20:12 |
Ayan
Messages: 11
Registered: February 2002
|
Junior Member |
|
|
use nvl(sum(deliveryqty),0) in following select clause
select sum(deliveryqty) into vschqty from saao2
where compcode=:new.compcode
and branchcd = :new.branchcd
and seriescd = :new.seriescd
and aono = :new.aono
and slno = :new.slno;
It might happen that in case there are no rows in second table then this select statement might result into no rows and vschqty have null value. Because of this reason your raise_application_error might fail
all the best
Ayan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
