Home » Developer & Programmer » Forms » Master-detail form / correct this trigger
Master-detail form / correct this trigger [message #440591] Tue, 26 January 2010 00:12 Go to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i am making a master detail form. i have problem to update quantity.

master table
desc sale_order;
ID NOT NULL NUMBER(7)
CUSTOMER_ID NUMBER(7)
DATE_ORDERD DATE
TOTAL NUMBER(11,2)
PAYMENT_TYPE VARCHAR2(6)

desc saleorder_detail;
ID NOT NULL NUMBER(7)
SO_ID NUMBER(7)
PRODUCT_ID NUMBER(7)
SALE_PRICE NUMBER(9,2)
QUANTITY NUMBER(9)

desc product;
PRODUCT_ID NOT NULL NUMBER(7)
DESCRIPTION VARCHAR2(50)
QTY_ON_HAND NUMBER(Cool
COST_PRICE NUMBER(9,2)
SALE_PRICE NUMBER(9,2)

i have been also attached sale_order form file.

[MERGED by LF]

[Updated on: Wed, 27 January 2010 03:27] by Moderator

Report message to a moderator

Re: Master detail form [message #440598 is a reply to message #440591] Tue, 26 January 2010 00:57 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the problem, actually?

Did you use Wizard to create master-detail form? If not, perhaps you should as it will do it correctly. Basic functionalities will work as expected (inserts, updates, deletes). You can add your own logic later, if necessary.
Re: Master detail form [message #440601 is a reply to message #440598] Tue, 26 January 2010 01:13 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
problem is that when i am running a form of cust_order and punch a quantity in :saleorder_detail.quantity.
it have been update a product table of negative value of qty_on_hand.

i am using of two trigger:
post-insert
post-update

post-inset syntax are;

declare
current_quantity number(8);
begin
current_quantity := :N_SORD_DETAIL.QUANTITY;
UPDATE N_PRODUCT
SET QTY_ON_HAND =
QTY_ON_HAND-CURRENT_QUANTITY
WHERE RTRIM(PRODUCT_ID) = :N_SORD_DETAIL.PRODUCT_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MESSAGE ('Error while updating details of new records');
raise form_trigger_failure;
end;

post-update syntax are;

declare
current_qty number(8);
begin
current_qty := :N_sord_detail.quantity;
update N_product
set qty_on_hand = qty_on_hand-current_qty
where rtrim(product_id) = :N_sord_detail.product_id;
end;

(if any product insufisent then display the message)
please correct this trigger [message #440609 is a reply to message #440591] Tue, 26 January 2010 02:48 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i have problem in this trigger;
data Block Level
object: order_detail
post-insert

declare
old_qty Number(8);
current_qty number(8);
begin
current_qty := :N_sord_detail.quantity;
select qty_on_hand into old_qty
from N_product;
if current_qty > old_qty
then
message('insuffisent Qunatity');
message('insuffisent Qunatity');
elsif
update N_product
SET qty_on_hand =
current_qty-old_qty
WHERE (PRODUCT_ID) = :N_SORD_DETAIL.PRODUCT_ID;
end if;
end;

[Updated on: Tue, 26 January 2010 03:01]

Report message to a moderator

Re: please correct this trigger [message #440611 is a reply to message #440609] Tue, 26 January 2010 03:39 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This
select qty_on_hand into old_qty
from N_product;
select quantity of ALL products in the table. I'd say that you forgot the WHERE clause.

elsif
update n_product
is wrong; should be ELSE, not ELSIF.

You don't need "current_qty" variable; use block value (":N_sord_detail.quantity") instead.
Re: Master-detail form / correct this trigger [message #440613 is a reply to message #440591] Tue, 26 January 2010 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
this
SET qty_on_hand =
current_qty-old_qty

Is the wrong way round. You should be subtracting the amount entered from the amount in the products table, and you don't need to use the old_qty variable either.
Re: please correct this trigger [message #440614 is a reply to message #440611] Tue, 26 January 2010 03:51 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
also display a error when i compile
(encountered the symbol "update"


declare
old_qty Number(8);
begin
current_qty := :N_sord_detail.quantity;
select qty_on_hand into old_qty
from N_product;
if :N_sord_detail.quantity > old_qty
then
message('insuffisent Qunatity');
message('insuffisent Qunatity');
elsif
update N_product
SET qty_on_hand =
:N_sord_detail.quantity - old_qty
WHERE (PRODUCT_ID) = :N_SORD_DETAIL.PRODUCT_ID;
endif;
end;

[Updated on: Tue, 26 January 2010 03:57]

Report message to a moderator

Re: please correct this trigger [message #440616 is a reply to message #440611] Tue, 26 January 2010 04:05 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Littlefoot wrote on Tue, 26 January 2010 09:39

elsif
update n_product
is wrong; should be ELSE, not ELSIF.

Re: please correct this trigger [message #440619 is a reply to message #440616] Tue, 26 January 2010 04:50 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
also display a error when i compile
(encountered the symbol "update"


declare
old_qty Number(8);
begin
current_qty := :N_sord_detail.quantity;
select qty_on_hand into old_qty
from N_product;
if :N_sord_detail.quantity > old_qty
then
message('insuffisent Qunatity');
message('insuffisent Qunatity');
elsif
update N_product
SET qty_on_hand =
:N_sord_detail.quantity - old_qty
WHERE (PRODUCT_ID) = :N_SORD_DETAIL.PRODUCT_ID;
endif;
end;
Re: Master-detail form / correct this trigger [message #440621 is a reply to message #440591] Tue, 26 January 2010 04:58 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Me and Littefoot have both pointed out what the problems are. reread our posts.
Re: Master-detail form / correct this trigger [message #440622 is a reply to message #440621] Tue, 26 January 2010 05:03 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i am also implement your comments but stil a same error
also display a error when i compile
(encountered the symbol "update"


declare
old_qty Number(8);
begin
current_qty := :N_sord_detail.quantity;
select qty_on_hand into old_qty
from N_product;
if :N_sord_detail.quantity > old_qty
then
message('insuffisent Qunatity');
message('insuffisent Qunatity');
elsif
update N_product
SET qty_on_hand =
:N_sord_detail.quantity - old_qty
WHERE (PRODUCT_ID) = :N_SORD_DETAIL.PRODUCT_ID;
endif;
end;

please change this trigger in your reply.
Re: Master-detail form / correct this trigger [message #440623 is a reply to message #440622] Tue, 26 January 2010 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
talk4ever wrote on Tue, 26 January 2010 11:03
i am also implement your comments but stil a same error
also display a error when i compile
(encountered the symbol "update"


declare
old_qty Number(8);
begin
current_qty := :N_sord_detail.quantity;
select qty_on_hand into old_qty
from N_product;
if :N_sord_detail.quantity > old_qty
then
message('insuffisent Qunatity');
message('insuffisent Qunatity');
elsif
update N_product
SET qty_on_hand =
:N_sord_detail.quantity - old_qty
WHERE (PRODUCT_ID) = :N_SORD_DETAIL.PRODUCT_ID;
endif;
end;

please change this trigger in your reply.

Mad No.
We've told you twice exactly what the problem is. If can't fix it from that then you are in the wrong job. So stop being lazy and sort it out.
Re: Master-detail form / correct this trigger [message #440756 is a reply to message #440591] Tue, 26 January 2010 22:57 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
declare
old_qty Number(8);
begin
select qty_on_hand into old_qty
from N_product;
if :N_sord_detail.quantity > old_qty
then
message('insuffisent Qunatity');
message('insuffisent Qunatity');
elsif
update N_product
SET qty_on_hand =
:N_sord_detail.quantity - old_qty
WHERE (PRODUCT_ID) = :N_SORD_DETAIL.PRODUCT_ID;
endif;
end;

(anyone can help me)
when i compile this trigger displaying a error message (encountered this symbol "update" when expecting one of the following.................)
help me [message #440778 is a reply to message #440591] Wed, 27 January 2010 00:17 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i m create form on sale_order
in this form two data block sale_order and sale_order_detail.
master block: sale_order
detail block: sale_order_detail

sale_order field:

ID NUMBER(7),
CUSTOMER_ID NUMBER(7),
DATE_ORDERD DATE,
TOTAL NUMBER(11,2),
PAYMENT_TYPE VARCHAR2(6),

sale_ord_detail field:

ID NUMBER(7),
SO_ID NUMBER(7),
PRODUCT_ID NUMBER(7),
Sale_PRICE NUMBER(9,2),
QUANTITY NUMBER(9),

product field:

product_ID Number (7),
DESCRIPTION VARCHAR2(50),
QTY_ON_HAND nUMBER (Cool,
COST_PRICE NUMBER (9,2),
SALE_PRICE NUMBER(9,2),

in this form customer order information.

i want that when i enter of any order in sale_order.

then it check how much i enter quantity in sale_ord_detail section and it minus qty_on_hand of product table.


i also attached the form screen.


i write this code for this purpose:


declare
old_qty Number(8);
begin
select qty_on_hand into old_qty
from product;
if :sale_ord_detail.quantity > old_qty
then
message('insuffisent Qunatity');
message('insuffisent Qunatity');
elsif
update product
SET qty_on_hand =
:sale_ord_detail.quantity - old_qty
WHERE (product.PRODUCT_ID) = :Sale_ORD_DETAIL.PRODUCT_ID;
endif;
end;
Re: Master-detail form / correct this trigger [message #440817 is a reply to message #440756] Wed, 27 January 2010 03:21 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you have some problems.

Tragedy is that Oracle related problems aren't the worst you have.
Re: Master-detail form / correct this trigger [message #440831 is a reply to message #440591] Wed, 27 January 2010 05:12 Go to previous messageGo to next message
talk4ever
Messages: 21
Registered: January 2010
Location: pakistan
Junior Member
i got a answer to ogher source.

correct trigger Name is Pre-insert
block level
syntax is:

DECLARE
old_qty NUMBER;
BEGIN
SELECT QTY_ON_HAND INTO old_qty
FROM N_PRODUCT
WHERE :N_SORD_DETAIL.PRODUCT_ID = N_PRODUCT.PRODUCT_ID;
if :N_SORD_DETAIL.QUANTITY > OLD_QTY THEN
MESSAGE('insuffisent Qunatity');
MESSAGE('insuffisent Qunatity');
RAISE FORM_TRIGGER_FAILURE;
ELSe
UPDATE N_PRODUCT SET QTY_ON_HAND = QTY_ON_HAND - :N_SORD_DETAIL.QUANTITY
WHERE N_PRODUCT.PRODUCT_ID = :N_SORD_DETAIL.PRODUCT_ID;
END IF;
end;


Re: Master-detail form / correct this trigger [message #440839 is a reply to message #440831] Wed, 27 January 2010 05:49 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Other source", eh? Congratulations. Now I'm ever more worried.
Re: Master-detail form / correct this trigger [message #445456 is a reply to message #440839] Mon, 01 March 2010 23:57 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
And not a 'code' tag between them.

David
Previous Topic: Can't open library : D2KWUT32.DLL
Next Topic: SELECT ALL
Goto Forum:
  


Current Time: Sun Sep 25 12:57:31 CDT 2016

Total time taken to generate the page: 0.29045 seconds