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. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Could someone post an example of an insert trigger?

    These are my assignment instructions:

    The Triggers
    Write a trigger on the UPDATE action of the INVENTORY table (UpdateInventoryTRG.sql). This trigger checks to see if there is enough stock on hand to meet the new line item quantity. If the Stockqty :)new.Stockqty) is insufficient, raise an exception. This requires that you declare a user defined exception in the UPDATE trigger and also in any module where you wish the exception to be raised.

    Write a trigger on the INSERT activity of the ORDERITEMS table (InsertOrderitemsTRG.sql). In this trigger you will determine the value of the column named Detail, which is one more than the last Detail for that Orderid (remember to code for no existing detail lines too). One of the beauties of Oracle triggers is that you can assign a value to a column :)new.Detail) of the newly inserted row. You can access the new row inside the trigger. Also, in this trigger you will perform the UPDATE to the INVENTORY table that reduces the Stockqty of the Partid being ordered by the amount in the new line item. Exception handling must be included here.


    I have not worked with triggers before so I am a bit lost.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Is this a database trigger or a forms trigger? The assignment states this trigger needs to be in every module where the exception is to be raised. A forms trigger is different from a database trigger; a database trigger would fire regardless of the module where a forms trigger is local to a module. Basically triggers are nothing more than PL/SQL code so if you're familiar with that this shouldn't be a real problem. An example of a database trigger follows; I haven't written forms triggers in quite a while so someone else may need to add to this discussion. The database trigger:


    Code (SQL):

    SQL> CREATE OR REPLACE TRIGGER stockqty_ck_trg
      2  BEFORE UPDATE ON inventory
      3  FOR each ROW
      4  DECLARE
      5          insuff_inv exception;
      6          pragma exception_init(insuff_inv, -20111);
      7          newqty  orderitems.qty%TYPE;
      8  BEGIN
      9          SELECT qty
     10          INTO newqty
     11          FROM orderitems
     12          WHERE partid = :NEW.partid
     13          AND orderid = 101;
     14
     15          IF :NEW.stockqty < newqty THEN
     16                  raise insuff_inv;
     17          END IF;
     18
     19  exception
     20          WHEN insuff_inv THEN
     21                  raise_application_error(-20111, 'Insufficient stock on-hand to complete order.', TRUE);
     22  END;
     23  /


    TRIGGER created.


    SQL>
    SQL> INSERT INTO orderitems
      2  VALUES(101, 998, 99, 7);


    1 ROW created.


    SQL>
    SQL> UPDATE inventory
      2  SET stockqty = stockqty - 7
      3  WHERE partid = 99;
    UPDATE inventory
           *
    ERROR at line 1:
    ORA-20111: Insufficient stock on-hand TO complete ORDER.
    ORA-06512: at "GRIBNAUT.STOCKQTY_CK_TRG", line 18
    ORA-20111:
    ORA-04088: error during execution OF TRIGGER 'GRIBNAUT.STOCKQTY_CK_TRG'

    SQL>
     

    This is, of course, an example and the table structure of my INVENTORY table probably does not match the structure of your INVENTORY table. Use it as a template to construct your trigger.
     
  3. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Thanks for the help. I don't think my trigger is triggering:

    Code (Text):
    CREATE or REPLACE TRIGGER trigger_update
      BEFORE UPDATE ON INVENTORY
       FOR EACH ROW

    DECLARE
      not_enough_qty EXCEPTION;
      vPartid NUMBER(4,0);
      vQuantity Number(4,0);
    BEGIN
      vPartid := &1;
      vQuantity := &2;
      IF(:NEW.STOCKQTY < vQuantity) THEN
        RAISE not_enough_qty;
        ROLLBACK;
      ELSE
      END IF;

    EXCEPTION
        WHEN not_enough_qty THEN
          DBMS_OUTPUT.put_line('Not enough quantity to execute - '||SQLERRM||'-'||SQLCODE);
    END;
     
    UPDATE INVENTORY
    SET STOCKQTY = STOCKQTY - vQuantity
    WHERE PARTID = vPartid;
       
    DBMS_OUTPUT.put_line('Update sucessful!');
    My output:
    Code (Text):
    TRIGGER TRIGGER_UPDATE compiled
    Errors: check compiler log
    What am I doing wrong? Update successful or the exception are not being raised
     
  4. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    vPartid := &1;
    vQuantity := &2;


    What do you expect these two lines to do?

    It's not clear from your post as to where the trigger text actually ends. Is it the whole thing, to include the SQL statement after the end? (Hope not) ... and why is there a dangling DBMS statement that doesn't appear to be attached to anything?
     
  5. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Hi, the update statement at the end is what triggers the update trigger? I am trying to update the table and execute the trigger, if successful it says successful.

    The &1 and &2 are input parameters that I will feed the script the values of partid and quantity.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):

    CREATE OR REPLACE TRIGGER trigger_update
      BEFORE UPDATE ON INVENTORY
       FOR EACH ROW
     
    DECLARE
      not_enough_qty EXCEPTION;
      vPartid NUMBER(4,0);
      vQuantity NUMBER(4,0);
    BEGIN
      vPartid := &1;
      vQuantity := &2;
      IF(:NEW.STOCKQTY < vQuantity) THEN
        RAISE not_enough_qty;
        ROLLBACK;
      ELSE
      END IF;
     
    EXCEPTION
        WHEN not_enough_qty THEN
          DBMS_OUTPUT.put_line('Not enough quantity to execute - '||SQLERRM||'-'||SQLCODE);
    END;
    /
     

    Your trigger isn't being created because you did not have the '/' on the last line; that character is necessary for PL/SQL units to compile.
     
  7. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Code (Text):


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

    DECLARE
      not_enough_qty EXCEPTION;
      vPartid NUMBER(4,0);
      vQuantity Number(4,0);
    BEGIN
      vPartid := &1;
      vQuantity := &2;
      IF(:NEW.STOCKQTY < vQuantity) THEN
        RAISE not_enough_qty;
        ROLLBACK;
      ELSE
      END IF;

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

    UPDATE INVENTORY
    SET STOCKQTY = STOCKQTY - vQuantity
    WHERE PARTID = vPartid;
       
    DBMS_OUTPUT.put_line('Update sucessful!');  
     

    Thanks. It compiled now, but I'm getting errors compiling.
    Code (Text):

    TRIGGER TRIGGER_UPDATE compiled
    Errors: check compiler log
    Error starting at line : 45 in command -
    UPDATE INVENTORY
    SET STOCKQTY = STOCKQTY - vQuantity
    WHERE PARTID = vPartid
    Error at Command Line : 47 Column : 16
    Error report -
    SQL Error: ORA-00904: "VPARTID": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:

    Error starting at line : 49 in command -
    DBMS_OUTPUT.put_line('Update sucessful!')
    Error report -
    Unknown Command

     
     
  8. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Why wont my script work?

    Code (Text):
     



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

    DECLARE
      not_enough_qty EXCEPTION;
      vPartid NUMBER(4,0);
    BEGIN
      vPartid := &PartID;
      IF(:NEW.STOCKQTY < 0) THEN
        RAISE not_enough_qty;
      ELSE
      END IF;

    EXCEPTION
        WHEN not_enough_qty THEN
          DBMS_OUTPUT.put_line('Not enough quantity to execute - '||SQLERRM||'-'||SQLCODE);
    END;
    /  
    DECLARE
      vQuantity NUMBER(4,0);
    BEGIN
      vQuantity:=&Quantity
     
    UPDATE INVENTORY
    SET STOCKQTY = STOCKQTY - vQuantity
    WHERE PARTID = vPartid;
       
    DBMS_OUTPUT.put_line('Update sucessful!');  

    END;

     
     
  9. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    First compile the below trigger code.

    Code (SQL):

    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;
      ELSE
      END IF;
     
    EXCEPTION
        WHEN not_enough_qty THEN
          DBMS_OUTPUT.put_line('Not enough quantity to execute - '||SQLERRM||'-'||SQLCODE);
    END;
    /  
     

    Once Trigger is successfully created then run the below code.

    Code (SQL):

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

    Regards
    Sambasiva Reddy.K
     
  10. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm getting compiling errors for both:

    Code (Text):

    Error(9,3): PLS-00103: Encountered the symbol "END" when expecting one of the following:     begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge pipe

    Error(9,3): PLS-00103: Encountered the symbol "END" when expecting one of the following:     begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge pipe

     
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Remove ELSE in trigger code.
     
    sambuduk likes this.
  12. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm still unable to get a successful update. Although it compiles now.
     
  13. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Are you getting error?? If so what is that??

    Regards
    Sambasiva Reddy.K
     
  14. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,


    DECLARE
    vQuantity NUMBER(4,0);
    BEGIN
    vQuantity:=&Quantity

    UPDATE INVENTORY
    SET STOCKQTY = STOCKQTY - vQuantity
    WHERE PARTID = vPartid;

    DBMS_OUTPUT.put_line('Update sucessful!');

    END;

    According to your trigger stockqty lessthan zero then only raise exception.

    here you are updating stockqty-vquantity.

    if stockquanatity =5 and vquantity =3 then new stockquantity=2 so it will not fire exception
    but if stockquanatity =5 and vquantity =6 then new stockquantity= -1
    then it will raise exception.
     
  15. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Right, but when it doesn't fire exception it should at least say "update successful". It does not.
     
  16. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm getting the compiler error:

    Error(14,1): PLS-00103: Encountered the symbol "DECLARE"
     
  17. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm getting the compiler error:

    Error(14,1): PLS-00103: Encountered the symbol "DECLARE"
     
  18. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    Which code you are compiling ? plz Post the code.

    Regards
    Sambasiva Reddy.K
     
  19. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Code (Text):

    SET SERVEROUTPUT ON;

    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;

     
    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 sucessful!');  

    END;

     
     
  20. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    Don't compile/run both codes at a time. First compile the Trigger code ( It is one time activity) Once it is compiled and Trigger gets created then run the below code.

    Code (SQL):


    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 sucessful!');  
     
    END;
    /

     

    Regards
    Sambasiva Reddy.K