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!

Example of Insert trigger

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

  1. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the code YOU are using -- we cannot answer such questions without knowing what Oracle is trying to process.
     
  2. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Am I able to keep both scripts in the same file?
     
  3. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Are you trolling this thread?

    I posted my code on post #19
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,176
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    first paste this code in your sql developer and select all and press F5.

    CREATE or REPLACE TRIGGER trigger_update
    BEFORE UPDATE ON INVENTORY
    FOR EACH ROW
    DECLARE
    not_enough_qty EXCEPTION;
    BEGIN
    IF:)NEW.STOCKQTY < 0) THEN
    RAISE not_enough_qty;
    END IF;
    EXCEPTION
    WHEN not_enough_qty THEN
    DBMS_OUTPUT.put_line('Not enough quantity to execute - '||SQLERRM||'-'||SQLCODE);
    END;


    Once it is compiled successfully then remove that code and paste below code and select all and press F5.


    DECLARE
    vPartid NUMBER(4,0);
    vQuantity NUMBER(4,0);
    BEGIN
    vPartid := &PartID;
    vQuantity:= &Quantity;
    UPDATE INVENTORY
    SET STOCKQTY = STOCKQTY - vQuantity
    WHERE PARTID = vPartid;
    DBMS_OUTPUT.put_line('Update successful!');
    END;
     
    pditty8811 likes this.
  5. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I fixed it by doing this removing everything except the trigger:

    Code (Text):



    --server out on
    SET SERVEROUTPUT ON;

    --Update INVENTORY trigger
    CREATE or REPLACE TRIGGER trigger_update
      BEFORE UPDATE ON INVENTORY
       FOR EACH ROW

    DECLARE
      not_enough_qty EXCEPTION;
    BEGIN
      IF(:NEW.STOCKQTY < 0) THEN
        RAISE not_enough_qty;
      END IF;

    EXCEPTION
        WHEN not_enough_qty THEN
          DBMS_OUTPUT.put_line('Not enough stock quantity to execute update - '||SQLERRM||'-'||SQLCODE);
    END;

     

    So it works.

    But now I put it in another file with another trigger. I need to declare :new.Detail bind variable.
    Look here:
    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;

     
    I get this error:

    Bind Variable "new" is NOT DECLARED
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,176
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Pulivendula
    No you can't declare like that, becoz :NEW,:OLD are the psudocolumns or oracle reserved keywords.
     
  7. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    My instructions are to grab the value of Detail column within the trigger_insert TRIGGER. How am I to do that? How about using vDetail as variable name like this:

    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);
    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 vDetail
      FROM ORDERITMES
      WHERE ORDERITEMS.ORDERID = 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;
     
    END;

     
    How do I get that to work in a trigger?
     
  8. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Anyone know?
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you only knew what a troll is. I am TRYING to assist you but with these responses I can understand why your assignments don't get good scores.
     
  10. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130

    Stop trolling my threads!
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Again, I am NOT trolling anything. It's a shame you won't realize this. And, again, you CANNOT declare anything using :new or :eek:ld as variables. Since the Detail column IS in the table you can simply use:


    if :new.Detail = ... then


    It does NOT need a declaration. Period.