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(
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 #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)
|
|
|
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 #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 #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;
|
|
|
|
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.
|
|
|
|
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.................)
|
|
|
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 (,
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 #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;
|
|
|
|
|