7 Replies Latest reply: May 21, 2012 9:42 AM by CraigB RSS

    Insert and Update Quantity

    Moazam Shareef
      Guyz,

      Can anyone help me to sort out my problem.

      I have the below table for items receiving.
      SQL> DESC MN_ARN_dET
       Name                                                                                Null?    Type
       ----------------------------------------------------------------------------------- -------- --------------
       INV_NO                                                                                       VARCHAR2(15)
       INV_DATE                                                                                     DATE
       ITEM_NO                                                                                      VARCHAR2(10)
       ITEM_DESCP                                                                                   VARCHAR2(100)
       QTY                                                                                          NUMBER(6)
       UOM                                                                                          VARCHAR2(10)
       PRICE                                                                                        NUMBER(9,2)
       CATEGORY                                                                                     VARCHAR2(15)
       RECD_UPD                                                                                     DATE
      Table for STOCK
      SQL> DESC MN_STK_DET
       Name                                                                                Null?    Type
       ----------------------------------------------------------------------------------- -------- -------------
       ITEM_NO                                                                                      VARCHAR2(15)
       ITEM_DESCP                                                                                   VARCHAR2(100)
       QTY                                                                                          NUMBER(6)
       UOM                                                                                          VARCHAR2(10)
      whenever user insert the record in MN_ARN_DET upon commit it should insert the same record in MN_STK_DET table if ITEM_NO exist in MN_STK_DET then just update the quantity.

      how can i solve this and which trigger i have to use? im using forms 6i.

      Regards
        • 1. Re: Insert and Update Quantity
          Moazam Shareef
          Guyz i'm inserting, updating the stock table with the below code while inserting records is ok and its update the MN_STK_DET table but on update its showing the below error. anyone help me to solve the problem.
          PROCEDURE UPD_QTY IS
            LN$Dummy PLS_INTEGER := 0 ;
          BEGIN
          
            -- Table MN_STK_DETIAL --
          
          BEGIN
               SELECT 1 INTO LN$DUMMY FROM MN_STK_DET
               WHERE EXISTS (SELECT ITEM_NO FROM MN_STK_DET WHERE ITEM_NO = :MN_ARN_DET.ITEM_NO) ;
                   -- Found -> update --
                 Message('Update STOCK table');
                 UPDATE MN_STK_DET
                 SET QTY= NVL(MN_sTK_DET.QTY,0) + NVL(:MN_ARN_DET.QTY,0)
                 WHERE  ITEM_NO=:MN_ARN_DET.ITEM_NO;
          EXCEPTION
                         WHEN NO_DATA_FOUND THEN
                    -- Not found -> insert --
                 Message('Insert into STOCK table');
          
                 INSERT INTO MN_STK_DET (ITEM_NO,ITEM_DESCP,QTY,UOM )
                 VALUES (:MN_ARN_DET.ITEM_NO,
                 :MN_ARN_DET.ITEM_DESCP,
                 :MN_ARN_DET.QTY,
                 :MN_ARN_DET.UOM);
          
            End ;
          END;
          I'm using the above procedure on PRE-INSERT trigger on block level
          UPD_QTY;
          while updating the record it showing the below error.
          FRM-40735: PRE-INSERT trigger raised unhandled exception ORA-01422
          I'm using forms 6i.

          Regards
          • 2. Re: Insert and Update Quantity
            Amatu Allah Neveen Ebrahim
            Hi Moazam
            whenever user insert the record in MN_ARN_DET upon commit it should insert the same record in MN_STK_DET table
            Pls Use POST-COMMIT or POST-INSERT Trigger and do the insert manually into MN_STK_DET table

            did u look for the error at google ? this is one of the most common errors pls look at the description & Action...
             
            Error:     ORA-01422: exact fetch returns more than requested number of rows
            
            Cause:     You tried to execute a SELECT INTO statement and more than one row was returned.
            
            Action:     Rewrite your SELECT INTO statement so that only one row is returned.
            i think u have to use a unique constraint or use a pk for item_no at least or u have to modify ur where statement to be more restricted according to the data displayed...

            u also have to handle this exception

             
            EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                  ---   Do something; error handling code when no record is found
                   WHEN TOO_MANY_ROWS THEN
                    --  Do something ; error handling code when too many records are found
                   NULL;
            Hope this helps...

            Regards,

            Amatu Allah
            • 3. Re: Insert and Update Quantity
              CraigB
              whenever user insert the record in MN_ARN_DET upon commit it should insert the same record in MN_STK_DET table if ITEM_NO exist in MN_STK_DET then just update the quantity.
              I would do this in an After-Insert trigger on the MN_ARN_DET table - not in Forms. Let the database do the work for you. :)

              Craig...
              • 4. Re: Insert and Update Quantity
                Moazam Shareef
                Thanks for your help Craig,

                can we use two database trigger here? 1- for inserting and 2- for updating? example as below.

                For Inserting:
                CREATE or REPLACE TRIGGER emp_after_insert
                 AFTER INSERT ON emp
                 FOR EACH ROW
                 DECLARE
                 BEGIN
                 insert into emp_backup values (:new.empid, :new.fname, :new.lname);
                 DBMS_OUTPUT.PUT_LINE('Record successfully inserted into emp_backup table');
                 END;
                for updating:
                CREATE or REPLACE TRIGGER emp_after_update
                 AFTER UPDATE OF empid ON emp
                 FOR EACH ROW
                 DECLARE
                 BEGIN
                 update emp_backup
                 set empid = :new.empid
                 where empid = ld.empid;
                 DBMS_OUTPUT.PUT_LINE('empid successfully updated into emp_backup table');
                 END;
                • 5. Re: Insert and Update Quantity
                  Amatu Allah Neveen Ebrahim
                  Hi

                  i think that doing it from the database will be much faster in performance as Carig said let the db does the db work...

                  maybe u mistaken or mis-copy
                  where empid = ld.empid;
                  Should be...
                  where empid =:old.empid;
                  But it's not variable in a cursor
                   
                  You cannot reference old values when inserting a record, or new values when deleting a record in a database Triggers.
                  Because they DO NOT  exist.

                  Pls check the restriction here...

                  This clarifies misunderstanding



                  Hope this helps...

                  Regards,

                  Amatu Allah
                  • 6. Re: Insert and Update Quantity
                    mo*404491*th
                    I suggest the following code using the DBT:-

                    Create or replate trigger upd_MN_STK_DET
                    after insert on MN_ARN_dET
                    for each row

                    begin

                    update MN_STK_DET
                    set qty = qty + :new.qty
                    where
                    item_no = :new.item_no and
                    uom = :new.uom;

                    if SQL%NOTFOUND then
                    insert into MN_STK_DET( ITEM_NO, ITEM_DESCP,QTY,UOM)
                    values(:new.item_no,:new.item_descp,:new.qty,:new.uom);
                    end if;

                    end;
                    /




                    ----------------------------------------------------------------------------------- -------- --------------
                    INV_NO VARCHAR2(15)
                    INV_DATE DATE
                    ITEM_NO VARCHAR2(10)
                    ITEM_DESCP VARCHAR2(100)
                    QTY NUMBER(6)
                    UOM VARCHAR2(10)
                    PRICE NUMBER(9,2)
                    CATEGORY VARCHAR2(15)
                    RECD_UPD DATE



                    Table for STOCK


                    SQL> DESC MN_STK_DET
                    Name Null? Type
                    ----------------------------------------------------------------------------------- -------- -------------
                    ITEM_NO VARCHAR2(15)
                    ITEM_DESCP VARCHAR2(100)
                    QTY NUMBER(6)
                    UOM
                    • 7. Re: Insert and Update Quantity
                      CraigB
                      Moazam Shareef,
                      Yes, you can have a After-Insert and After-Update trigger on your table. On a side-note, I would remove the the DBMS_OUTPUT from your triggers as you will never see the output.

                      Craig...