Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    10

    Unanswered: Trigger update help

    i have three tables: ot_req, ot_po_breakup, and ot_po.

    when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates
    a seperate reocrd in ot_po_breakup with the details of ot_req
    ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is ok
    and when i insert also the logic is okay.

    i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_po
    in order of pb_ri_sys_id and pb_pi_sys_id and try to update the values
    in the columns pb_ves1q, pb_ves2q, and pb_ves3q order.

    i am trying to take the quantities in ot_po.pi_qty
    and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of
    ot_po_breakup where those columns are empty (0 or null)
    such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.

    My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column of ot_po.pi_qty
    the qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be
    equal to pb_ri_qty and do the same for delete as well

    --but i am having problem with update

    Code:
    CREATE TABLE OT_REQ
    (
      RI_ITEM    VARCHAR2(20 BYTE),
      RI_SYS_ID  NUMBER,
      RI_QTY     NUMBER
    )
    
    
    
    insert into ot_req values ('aa',1,20);
    
    
    
    CREATE TABLE OT_PO
    (
      PI_ITEM    VARCHAR2(12 BYTE),
      PI_QTY     NUMBER,
      PI_SYS_ID  NUMBER
    )
    
    
    
    
    INSERT INTO OT_PO values('aa',10,33)
    
    INSERT INTO OT_PO VALUES ('aa',10,34)
    
    
    CREATE TABLE OT_PO_BREAKUP
    (
      PB_RI_SYS_ID  NUMBER,
      PB_PI_SYS_ID  NUMBER,
      PB_RI_QTY     NUMBER,
      PB_VES1Q      NUMBER,
      PB_VES2Q      NUMBER,
      PB_VES3Q      NUMBER
    )
    
    
    SELECT * FROM  OT_PO_BREAKUP
    
    
    SQL> SELECT * FROM OT_PO_BREAKUP;
    
    PB_RI_SYS_ID PB_PI_SYS_ID  PB_RI_QTY   PB_VES1Q   PB_VES2Q   PB_VES3Q
    ------------ ------------ ---------- ---------- ---------- ----------
               1           33         10         10
               1           34         10         10
    
    
    --for inserting
    * Formatted on 2012/04/28 10:00 (Formatter Plus v4.8.8) */
    CREATE OR REPLACE TRIGGER ot_po_air
       AFTER INSERT
       ON ot_po
       FOR EACH ROW
    DECLARE
       v_pi_qty      NUMBER := NVL (:NEW.pi_qty, 0);    -- quantity to distribute
       v_pb_ri_qty   NUMBER;                                   -- space available
       v_least       NUMBER;
    -- least of quantity to distribute and space available
    BEGIN
       -- fill ot_po_breakup rows in order of pb_ri_sys_id:
       FOR r IN (SELECT   *
                     FROM ot_po_breakup
                 ORDER BY pb_ri_sys_id)
       LOOP
          -- calculate currently available space in this row:
          v_pb_ri_qty :=
               NVL (r.pb_ri_qty, 0)
             - NVL (r.pb_ves1q, 0)
             - NVL (r.pb_ves2q, 0)
             - NVL (r.pb_ves3q, 0);
          -- calculate least of quantity to distribute and space available:
          v_least := LEAST (v_pi_qty, v_pb_ri_qty);
    
          -- fill vessels with above qantity in order:  pb_ves1q, pb_ves2q, pb_ves3q:
          IF v_least > 0
          THEN
             IF NVL (r.pb_ves1q, 0) = 0
             THEN
                UPDATE ot_po_breakup
                   SET pb_ves1q = v_least
                 WHERE pb_ri_sys_id = r.pb_ri_sys_id
                   AND pb_pi_sys_id = r.pb_pi_sys_id;
             ELSIF NVL (r.pb_ves2q, 0) = 0
             THEN
                UPDATE ot_po_breakup
                   SET pb_ves2q = v_least
                 WHERE pb_ri_sys_id = r.pb_ri_sys_id
                   AND pb_pi_sys_id = r.pb_pi_sys_id;
             ELSIF NVL (r.pb_ves3q, 0) = 0
             THEN
                UPDATE ot_po_breakup
                   SET pb_ves3q = v_least
                 WHERE pb_ri_sys_id = r.pb_ri_sys_id
                   AND pb_pi_sys_id = r.pb_pi_sys_id;
             END IF;
    
             -- update quantity to distribute:
             v_pi_qty := v_pi_qty - v_least;
          END IF;
       END LOOP;
    END ot_po_air;
    
    --for deleting
    
    CREATE OR REPLACE TRIGGER ot_po_del
       AFTER DELETE
       ON ot_po
       FOR EACH ROW
    DECLARE
       CURSOR c1
       IS
          SELECT   *
              FROM ot_po_breakup
             WHERE pb_pi_sys_id = :old.pi_sys_id
          ORDER BY pb_ri_sys_id;
    BEGIN
       -- fill ot_po_breakup rows in order of pb_ri_sys_id:
       FOR i IN c1
       LOOP
          IF NVL (i.pb_ves1q, 0) != 0
          THEN
             UPDATE ot_po_breakup
                SET pb_ves1q = NULL
              WHERE pb_pi_sys_id = i.pb_pi_sys_id;
          ELSIF NVL (I.pb_ves2q, 0) != 0
          THEN
             UPDATE ot_po_breakup
                SET pb_ves2q = NULL
              WHERE  pb_pi_sys_id = i.pb_pi_sys_id;
          ELSIF NVL (I.pb_ves3q, 0) != 0
          THEN
             UPDATE ot_po_breakup
                SET pb_ves3q = NULL
              WHERE  pb_pi_sys_id = i.pb_pi_sys_id;
          END IF;
       END LOOP;
    END ot_po_del;
    
    
    --FOR UPDATE
    
    CREATE OR REPLACE TRIGGER ot_po_up
       AFTER UPDATE
       ON ot_po
       FOR EACH ROW
    DECLARE
       v_pi_qty      NUMBER;
       v_pb_ri_qty   NUMBER;
       v_least       NUMBER;
    
       CURSOR c1
       IS
          SELECT *
            FROM ot_po_breakup
           WHERE pb_pi_sys_id = :NEW.pi_sys_id AND pb_pi_sys_id = :OLD.pi_sys_id;
    BEGIN
       v_pi_qty := v_pi_qty + NVL (:NEW.pi_qty, 0) - NVL (:OLD.pi_qty, 0);
    
       -- fill ot_po_breakup rows in order of pb_ri_sys_id:
       FOR i IN c1
       LOOP
          -- calculate currently available space in this row:
          v_pb_ri_qty :=
               NVL (i.pb_ri_qty, 0)
             - NVL (i.pb_ves1q, 0)
             - NVL (i.pb_ves2q, 0)
             - NVL (i.pb_ves3q, 0);
          -- calculate least of quantity to distribute and space available:
          v_least := LEAST (v_pi_qty, v_pb_ri_qty);
          dummy1 ('LEAST' || '-' || v_least);
    
       -- fill vessels with above qantity in order:  pb_ves1q, pb_ves2q, pb_ves3q:
          IF v_least > 0
          THEN
             IF NVL (i.pb_ves1q, 0) != 0
             THEN
                UPDATE ot_po_breakup
                   SET pb_ves1q = v_least
                 WHERE pb_pi_sys_id = i.pb_pi_sys_id;
             ELSIF NVL (i.pb_ves2q, 0) != 0
             THEN
                UPDATE ot_po_breakup
                   SET pb_ves2q = v_least
                 WHERE pb_pi_sys_id = i.pb_pi_sys_id;
             ELSIF NVL (i.pb_ves3q, 0) != 0
             THEN
                UPDATE ot_po_breakup
                   SET pb_ves3q = v_least
                 WHERE pb_pi_sys_id = i.pb_pi_sys_id;
             END IF;
    
             -- update quantity to distribute:
             v_pi_qty := v_pi_qty - v_least;
          END IF;
       END LOOP;
    END ot_po_up;

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    What is the exception/error message you are receiving?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •