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 do I declare a new. bind variable?

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

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    new.Detail is the variable I want to define and initiate.

    Code (Text):

    SET SERVEROUTPUT ON;

    DECLARE
      vOrderid NUMBER(4,0);
      vPartid NUMBER(4,0);
      vQuantity NUMBER(4,0);
      vDetail NUMBER(2,0);
    BEGIN
      vOrderid := &1;
      vPartid := &2;
      vQuantity := &3;
    END;

    CREATE or REPLACE TRIGGER trigger_insert
      BEFORE INSERT ON ORDERITEMS
       FOR EACH ROW

    DECLARE
      VARIABLE new.Detail NUMBER(2,0);

    BEGIN
       
      SELECT (ORDERITEMS.DETAIL + 1)
      INTO :new.Detail
      FROM ORDERITMES
      WHERE orderitems.ORDERID = vOrderid;
     
      IF :new.Detail IS NULL THEN
          :new.Detail := 1;
      END IF;

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

    BEGIN
      INSERT INTO ORDERITEMS
      (ORDERID,
      DETAIL,
      PARTID,
      QTY)
      VALUES
      (vOrderid,
      :new.Detail,
      vPartid,
      vQuantity);
      DBMS_OUTPUT.put_line('Insert Successful!');
     
      START UpdateInventoryTRG.sql vPartid vQuantity;

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

     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot add fields to the query block within a PL/SQL trigger. The :new and :eek:ld blocks refer to query values from the table the trigger is created against; unless there is a Detail column in that table you won't be able to see or use :new.Detail by trying to declare it. And VARIABLE is a SQL*Plus command, not part of PL/SQL.
     
  3. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    There is a detail column, so how do I declare the :new.Detail variable because I'm getting this error:

    Bind Variable "new" is NOT DECLARED



    Code (Text):

    --compile UpdateInventoryTRG.sql
    START UpdateInventoryTRG.sql;

    --server output on
    SET SERVEROUTPUT ON;

    DECLARE
      vDetail NUMBER(4,0);
      vOrderid NUMBER(4,0);
     
    BEGIN
      vOrderid := &1;
     
    --Update INVETORY table, firing UpdateInventoryTRG.sql
    DECLARE

      vPartid NUMBER(4,0);
      vQuantity NUMBER(4,0);
      vDetail NUMBER(4,0);
    BEGIN

      vPartid := &2;
      vQuantity:= &3;
       
      UPDATE INVENTORY
      SET STOCKQTY = STOCKQTY - vQuantity
      WHERE PARTID = vPartid;
       
      DBMS_OUTPUT.put_line('Update to INVENTORY table sucessful!');  
      COMMIT;
    END;


    CREATE or REPLACE TRIGGER trigger_insert
      BEFORE INSERT ON ORDERITEMS
      FOR EACH ROW
    BEGIN
      --Find value of Detail
      SELECT (ORDERITEMS.DETAIL + 1)
      INTO :new.Detail
      FROM ORDERITMES
      WHERE ORDERITEMS.ORDERID = vOrderid;
         
      --Assign detail value of 1 if previous detail value is null
      IF :new.Detail IS NULL THEN
          :new.Detail := 1;
        END IF;
       
    EXCEPTION
      --general exception
      WHEN others THEN
        DBMS_OUTPUT.put_line('General Error - '||SQLERRM||'-'||SQLCODE);
    END;

    END;

     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't, period. If it's in the column list from the table then both the :new and :eek:ld blocks will have it. You CAN reference it:


    if :new.Detail <> 'Wombat soup du j our' then ...


    It is not a variable to declare.