OraFAQ Forum: Forms » Master-detail form / correct this trigger

Home » Developer & Programmer » Forms » Master-detail form / correct this trigger

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

Master-detail form / correct this trigger [message #440591]

Tue, 26 January 2010 00:12

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

Cool

COST_PRICE NUMBER(9,2)
SALE_PRICE NUMBER(9,2)

i have been also attached sale_order form file.

[MERGED by LF]

Attachment: Cust_Order.fmb
(Size: 116.00KB, Downloaded 1126 times)

[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

Littlefoot
Messages: 21818
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.

Report message to a moderator

Re: Master detail form [message #440601 is a reply to message #440598]

Tue, 26 January 2010 01:13

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)

Report message to a moderator

please correct this trigger [message #440609 is a reply to message #440591]

Tue, 26 January 2010 02:48

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

Littlefoot
Messages: 21818
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.

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440613 is a reply to message #440591]

Tue, 26 January 2010 03:42

cookiemonster
Messages: 13938
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.

Report message to a moderator

Re: please correct this trigger [message #440614 is a reply to message #440611]

Tue, 26 January 2010 03:51

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

cookiemonster
Messages: 13938
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.

Report message to a moderator

Re: please correct this trigger [message #440619 is a reply to message #440616]

Tue, 26 January 2010 04:50

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;

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440621 is a reply to message #440591]

Tue, 26 January 2010 04:58

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

Me and Littefoot have both pointed out what the problems are. reread our posts.

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440622 is a reply to message #440621]

Tue, 26 January 2010 05:03

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.

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440623 is a reply to message #440622]

Tue, 26 January 2010 05:14

cookiemonster
Messages: 13938
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.

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440756 is a reply to message #440591]

Tue, 26 January 2010 22:57

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.................)

Report message to a moderator

help me [message #440778 is a reply to message #440591]

Wed, 27 January 2010 00:17

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

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;

Attachment: sale_order.JPG
(Size: 50.32KB, Downloaded 587 times)

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440817 is a reply to message #440756]

Wed, 27 January 2010 03:21

Littlefoot
Messages: 21818
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.

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440831 is a reply to message #440591]

Wed, 27 January 2010 05:12

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;

Report message to a moderator

Re: Master-detail form / correct this trigger [message #440839 is a reply to message #440831]

Wed, 27 January 2010 05:49

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"Other source", eh? Congratulations. Now I'm ever more worried.

Report message to a moderator

Re: Master-detail form / correct this trigger [message #445456 is a reply to message #440839]

Mon, 01 March 2010 23:57

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

And not a 'code' tag between them.

David

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:23:34 CDT 2024