master - details tables

[tyb]

dear frends

i've a master table "purchase_orders" which has a quantity column
and a detailed table "styles" containing a quantity column

now the quantity column in "Purchase_order" is the sum of all the quantities in the "styles" relavant to that Purchase Order in the styles table.

i want the PO quantities to be updated with the summ of all the styles quantities on either Insert, update or delete.

plz help me

thanks in advance.

 

[HimanB]

Hi,
Write an "After INSERT or UPDATE or DELETE trigger for Each row" on Styles table.


If INSERT then
L_qty:=:new.qty; --- Qty of Styles table.
Update PO table
set qty=qty+L_qty
where ...........
elsif UPDATE then
L_qty:=:new.qty - ld.qty;
Update PO table
set qty=qty+L_qty
where ...........
else
L_qty:=ld.qty;
Update PO table
set qty=qty - L_qty
where ...........
end If;

HTH
Regards
Himanshu

 

[BJCooperIT]

Having a child table update the parent row may cause locking problems. It will cause problems with "record updated by another user" in forms. It will also degrade performance.

This is a non-normalized database design which should be avoided if possible.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[tyb]

thanks Himan & BJ for ur responses.

Himan !

i dont know how to write a trigger.

is it 2b written in sql or forms. also plz enclose the whole body,

many cardial thanx

 

[HimanB]

Hi,
Here I am talking about Database triggers.
So you need to fire the following Script in your Database directly to create a trigger Named ALST0001 which will fire whenever you Insert/Update or Delete record from Styles table.
Also do not forget to change the where clause in this script as i do not know on what keys you have made relationship between your tables.

Regards
Himanshu

CREATE OR REPLACE TRIGGER ALST0001
    AFTER
    INSERT OR UPDATE OR DELETE ON STYLES
    FOR EACH ROW
DECLARE
 L_QTY  NUMBER(10);
BEGIN
   IF INSERTING THEN
      L_QTY:=:NEW.QTY;
      UPDATE PURCHASE_ORDERS
      SET QTY = QTY + L_QTY
      WHERE ORDERNO = :NEW.ORDERNO; /**OR WHATEVER THE FEILDS ON WHICH YOU HAVE A RELATION BETEWEEN YOUR TABLES**/
   ELSIF UPDATING THEN
      L_QTY:=:NEW.QTY - :OLD.QTY; /**IF YOU AR INCREASING QTY IN STYLES THEN THIS +VE IF DECREASING THEN -VE **/
      UPDATE PURCHASE_ORDERS
      SET QTY = QTY + L_QTY
      WHERE ORDERNO = :NEW.ORDERNO; /**OR WHATEVER THE FEILDS ON WHICH YOU HAVE A RELATION BETEWEEN YOUR TABLES**/ 
   ELSE
      L_QTY:=:OLD.QTY;
      UPDATE PURCHASE_ORDERS
      SET QTY = QTY - L_QTY
      WHERE ORDERNO = :NEW.ORDERNO; /**OR WHATEVER THE FEILDS ON WHICH YOU HAVE A RELATION BETEWEEN YOUR TABLES**/ 
   END IF;
END;