1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to insert row into Table without data of one column?

Discussion in 'SQL PL/SQL' started by pditty8811, Feb 27, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    How to insert row into Table without data of one column?

    The table has four columns ORDERID, DETAIL, PARTID, and QTY. But I don't have the data for Detail column. How do I insert a row without inserting data in DETAIL column? The table does not accept null values.

    These are my assignment instructions:
    Since the value of the Detail column is determined inside of the INSERT trigger, you will provide column names on the INSERT command for just the three columns for which you have data. Remember, when you write an INSERT where the values inserted do not include every column in the table, you need to include the column names within the INSERT statement.

    Code (Text):
    INSERT INTO ORDERITEMS
        (ORDERID, PARTID, QTY)
        VALUES
        (vOrderid, vPartid, vQuantity);
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You completely miss half of the requirement:


    There is an INSERT trigger that populates the vDetail column (or there is supposed to be). If that is the case then you insert into the table using the remaining three columns as you illustrated.
     
  4. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    here is my insert trigger:

    Code (Text):

    CREATE or REPLACE TRIGGER trigger_insert
      BEFORE INSERT ON ORDERITEMS
      FOR EACH ROW
    DECLARE
      vPartid NUMBER(4,0);
      vDetail NUMBER(2,0);
      vOrderid NUMBER(4,0);
    BEGIN
     vPartid:=&1;
     vOrderid:=&3;
      --Find value of Detail
     SELECT (ORDERITEMS.DETAIL + 1)
     INTO vDetail
     FROM ORDERITEMS
     WHERE ORDERID = vOrderid AND PARTID = vPartid;
      DBMS_OUTPUT.put_line(vOrderid);    
      --Assign detail value of 1 if previous detail value is null
      IF vDetail IS NULL THEN
          vDetail := 1;
      END IF;

    EXCEPTION
      --general exception
      WHEN others THEN
        DBMS_OUTPUT.put_line('General Error - '||SQLERRM||'-'||SQLCODE);
    END;
     

    here is my insert:

    Code (Text):


       --Insert into ORDERITEMS table
        INSERT INTO ORDERITEMS
        (ORDERID, DETAIL, PARTID, QTY)
        VALUES
        (vOrderid, vDetail, vPartid, vQuantity);
     
    Am I doing anything wrong?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes. You can't use & variables in a trigger because there is no way to populate them. It should be a very simple trigger to write:


    Code (SQL):

    CREATE OR REPLACE TRIGGER trigger_insert
      BEFORE INSERT ON ORDERITEMS
      FOR EACH ROW
    BEGIN
     :NEW.Detail:=nvl(:NEW.Detail,0)+1;
    EXCEPTION
      --general exception
      WHEN others THEN
        DBMS_OUTPUT.put_line('General Error - '||SQLERRM||'-'||SQLCODE);
    END;
    /