1 2 Previous Next 15 Replies Latest reply on May 3, 2014 8:47 PM by EdStevens

    Trigger not working.

    Praveen Kumar Singh

      this trigger is not working properly..it always updating the value on 2nd time when i run the same insert query.

      CREATE OR REPLACE TRIGGER ORDER_TABLE_TRIGGER BEFORE INSERT ON ORDER_TABLE
      FOR EACH ROW
      DECLARE
      T_PRODUCT_DESC VARCHAR2(25);
      T_PRICE_PER_UNIT NUMBER(4);
      vMsg VARCHAR2(30) := 'Select Statment Trigger Fired';
      vMsg1 VARCHAR2(30) := 'Insert Statment Trigger Fired';
      vMsg2 VARCHAR2(30) := '************';
      vMsg3 VARCHAR2(30) := 'Trigger Fired completed';
      
      
      BEGIN
      dbms_output.put_line(vMsg);
      SELECT PRODUCT_DESC INTO T_PRODUCT_DESC FROM PRODUCT WHERE PRODUCT_ID= :new.PRODUCT_ID;
      SELECT PRICE_PER_UNIT INTO T_PRICE_PER_UNIT FROM PRODUCT WHERE PRODUCT_ID= :new.PRODUCT_ID;
      dbms_output.put_line(T_PRODUCT_DESC);
      dbms_output.put_line(T_PRICE_PER_UNIT);
      dbms_output.put_line(vMsg1);
      INSERT INTO ORDER_TABLE
      REMARK UPDATE ORDER_TABLE SET PRODUCT_DESC= T_PRODUCT_DESC;
      REMARK UPDATE ORDER_TABLE SET UNIT_COST= T_PRICE_PER_UNIT;
      
      
      dbms_output.put_line(vMsg2);
      UPDATE ORDER_TABLE SET PRICE= T_PRICE_PER_UNIT*:NEW.QUANTITY;
      
      
      dbms_output.put_line(vMsg3);
      END;
      
      
      

       

      Message was edited by: Praveen Kumar Singh

        • 1. Re: Trigger not working.
          rp0428

          this trigger is not working properly..it always updating the value on 2nd time when i run the same insert query.

          Consider yourself lucky that the trigger does NOT work. It has some SERIOUS flaws in both the design and implementation.

          SELECT PRODUCT_DESC INTO T_PRODUCT_DESC FROM PRODUCT WHERE PRODUCT_ID= :new.PRODUCT_ID; 

          SELECT PRICE_PER_UNIT INTO T_PRICE_PER_UNIT FROM PRODUCT WHERE PRODUCT_ID= :new.PRODUCT_ID; 

          Why are you using TWO queries? Just use one query and query both values.

          INSERT INTO ORDER_TABLE 

          Huh? You are in the INSERT trigger for that table. Why are you doing ANOTHER insert? You can NOT do DML for a table from within a trigger on that table.

           

          Where did you find PL/SQL syntax for a 'REMARK' statement?

          REMARK UPDATE ORDER_TABLE SET PRODUCT_DESC= T_PRODUCT_DESC;  

          REMARK UPDATE ORDER_TABLE SET UNIT_COST= T_PRICE_PER_UNIT;  

          That code won't compile so it can't be a trigger that actuall does an INSERT.

           

          UPDATE ORDER_TABLE SET PRICE= T_PRICE_PER_UNIT*:NEW.QUANTITY; 

          Why are you updating EVERY ROW in that table?

           

          Start over and tell us: what PROBLEM you are trying to solve?

          1 person found this helpful
          • 2. Re: Trigger not working.
            Praveen Kumar Singh

            Huh? You are in the INSERT trigger for that table. Why are you doing ANOTHER insert? You can NOT do DML for a table from within a trigger on that table.--THIS POINT I DIDN'T UNDERSTND

            This is just my 2nd Trigger which i am trying to right so that there are many thing wrong. it will be nice if you can suggest some good examples so that i can avoid these mistakes. since you seems to an expert on it.

             

            Message was edited by: Praveen Kumar Singh

            • 3. Re: Trigger not working.
              K.S.I.

              The trigger is written with errors and in such look it won't work.

               

              Yes, in the trigger on the table you can't perform DML operation over the same table

               

              Note:

              ORA-04091

              Mutating table - Oracle FAQ

              ORACLE-BASE - Oracle9i Mutating Table Exceptions

               

               

               

              What logic you try to implement?

               

               

              See also :

               

              Using Triggers

              Instead Trigger

              • 4. Re: Trigger not working.
                Praveen Kumar Singh

                Hi, I am trying to work on a trigger which will select product desc & price per unit from product master. i need to insert same in order_table and with Price based on product selected it quantity and unite price. this is what my above trigger code is about it...from above advice i cam to know that i am doing it wrong way. please suggest me the correct way of doing the same. PKS

                • 5. Re: Trigger not working.
                  brunovroman

                  Hello Praveen,

                   

                  you want maybe something like this:

                  a) check when an order is inserted that the corresponding product exists

                  b) show the description and price of the relevant product

                  c) compute the price of the order as "price of the product" * quantity ordered

                   

                  the "a)" is best achieved with a "referential integrity constraint" (a "foreign key") making sure that "order.product_id" (if not null) exists in product (as "product.product_id")

                   

                  the "b)" is a bit weird: using "dbms_output" in a trigger is a strange idea. Note that orders might be inserted from a sqlplus session, but also from many other origins that have no access to "dbms_output" output.

                  and even if you are happy with sqlplus, don't forget that an insert can be rolled back but that the output once sent out is sent out (can be misleading)

                   

                  the "c)" is not very "relational database" as the info is precisely in the 2 fields "product.price_per_unit" and "order.quantity".

                  What if the "price_per_unit" is modified in the table product? Would you like to revisit all the orders about the given product? Or maybe you preciseley want to store the price as it was when the order was inserted? And what about an update of the order modifying the quantity? Would you like to keep the price fixed at its original value?

                   

                  About the question of writing "insert into order_table" inside the trigger fired "before insert on order_table": you have to understand that this doesn't make sense. The application (example: your SQL*Plus session) performs the "INSERT INTO order_table", and the trigger has only to perform additional tasks or checks that you want to run in case of insertion, but for sure this doesn't include an additional insertion of rows in the same table...

                  Also: you don't have to update the table order_table inside the trigger, maybe you want to compute one field of the row currently inserted -> a simple assignation :new.price := ... is enough.

                   

                  Anyway, to give an example of trigger (but once again: bad code, what if a row is updated, ...):

                  CREATE OR REPLACE TRIGGER mytrigger

                    BEFORE INSERT ON order_table

                    FOR EACH ROW

                  DECLARE

                    t_desc  VARCHAR2(25); /* should be product.product_id%TYPE */

                    t_price NUMBER(4); /* should be product.price_per_unit%TYPE */

                  BEGIN

                    SELECT product_desc, price_per_unit

                      INTO t_desc, t_price

                      FROM product p

                      WHERE p.product_id = :new.product_id

                    ;

                    DBMS_OUTPUT.PUT_LINE( 'Prod:' || t_desc || '; price:' || TO_CHAR( t_price ) );

                    :new.price := t_price * :new.quantity;

                  END;

                  /

                   

                  Keep in mind that triggers are nice but have a major drawback: they are "hidden" and are easily forgotten. For example a developer might in the future write an application that would compute a price reduction for the customer and do something equivalent to "insert into order_table( ..., quantity, price, ... )  VALUES ( ..., 10, 10 * unit_price * 95%, ... )"

                  but your trigger will silently force the price to "full price"...

                  There are more drawbacks to triggers, I can only strongly suggest you to have an attentive look at Tom Kyte's column "The Trouble with Triggers".

                   

                  Best regards,

                   

                  Bruno Vroman.

                  • 6. Re: Trigger not working.
                    Praveen Kumar Singh

                    Hi Thanks for such detail reply..i got the point that trigger do have drawback specially because of frond end developer or development. I am doing this just for the learning purpose as i am new with oracle. Thanks once again. but still my problem and confused with above trigger is not solved.

                    • 7. Re: Trigger not working.
                      Zlatko Sirotic

                      CREATE OR REPLACE TRIGGER bir_order_table

                        BEFORE INSERT ON order_table

                        FOR EACH ROW

                      BEGIN

                        SELECT product_desc, price_per_unit, price_per_unit * :NEW.quantity

                          INTO :NEW.product_desc, :NEW.unit_cost, :NEW.price

                          FROM product

                         WHERE product_id = :NEW.product_id;

                      END;

                      /

                       

                      Regards

                      • 8. Re: Trigger not working.
                        brunovroman

                        Hi Praveen,

                         

                        "... just for the learning purpose ..." : it is indeed for this reason that I have posted some code, it should NOT be used "in real life"

                        "... still my problem ... trigger is not solved.": what do you mean? The initial code doesn't work and cannot work, but the code I have given works (once again: for learning purpose only; insert some rows, then do some "updates" (modifying quantities, or product_id...) and look at the content of the table: it will contain wrong data. An exercise might be to write a trigger "on update" that keeps the "total price" correct if the product_id or the quantity is modified. And you might think at two options for the unit_price: keep the unit_price that was used at insertion time or go to the product table and pick the "current" unit_price (ant to test: insert orders, then update "products" to modify the unit price of a product, then update order_table for an order about this product...)

                         

                        Good luck with Oracle,

                         

                        Bruno.

                        1 person found this helpful
                        • 9. Re: Trigger not working.
                          Praveen Kumar Singh

                          Thanks Everyone i modified it on the bases of your suggestion and now my Trigger is working As i want it to be,

                           

                          CREATE OR REPLACE TRIGGER ORDER_TABLE_TRIGGER BEFORE INSERT ON ORDER_TABLE
                           FOR EACH ROW
                           BEGIN
                           SELECT PRODUCT_DESC, PRICE_PER_UNIT, PRICE_PER_UNIT * :NEW.QUANTITY INTO :NEW.PRODUCT_DESC,:NEW.UNIT_COST,:NEW.PRICE FROM
                           PRODUCT WHERE PRODUCT_ID=:new.PRODUCT_ID; END;
                          
                          • 10. Re: Trigger not working.
                            brunovroman

                            Hello,

                             

                            the fact that product_desc and unit_cost are present also in the table order_table is really a design mistake... In a relational database an information is supposed to be written only one time, and denormalization exceptions have to be thoughtfully understood and justified (for example we might admit the "total price computed as "unit_price from table product * quantity", but I cannot imagine a justification for something like "product description").
                            What if you have a product that is used in 3000 orders? You will copy the product description 3000 times? And what if the product description is later modified in the table product?

                             

                            Best regards,

                             

                            Bruno.

                            • 11. Re: Trigger not working.
                              EdStevens

                              Praveen Kumar Singh wrote:

                               

                              Huh? You are in the INSERT trigger for that table. Why are you doing ANOTHER insert? You can NOT do DML for a table from within a trigger on that table.--THIS POINT I DIDN'T UNDERSTND

                              <snip>

                               

                              Think carefully about what fires your trigger, and what your trigger does.

                               

                              CREATE OR REPLACE TRIGGER ORDER_TABLE_TRIGGER BEFORE INSERT ON ORDER_TABLE 

                                  FOR EACH ROW 

                               

                              INSERT INTO ORDER_TABLE  ;

                               

                              So, your application does an insert into ORDER_TABLE.

                              That insert fires a trigger.

                              Which does an insert into ORDER_TABLE

                              Which fires a trigger

                              Which does an insert into ORDER_TABLE

                              Which fires a trigger

                              Which does an insert into ORDER_TABLE

                              Which fires a trigger

                              ad infinitum, ad nauseum.  Until oracle traps a mutating table error and kicks you out.

                               

                              In the old days we used to engage in an activity called 'desk checking'.  I leave the details as an exercise for the student.

                              • 12. Re: Trigger not working.
                                Zlatko Sirotic

                                Two words about the design of your tables.

                                 

                                Having product_desc column in a table order_table is ok if you need to store the original description of the product.

                                Another way is to have a table product_history.

                                Having product_desc in product_history is cleaner, having product_desc in order_table is easier / faster.

                                 

                                In a order_table, you don't need all three columns quantity, unit_cost and price (= amount).

                                One of the three of them, e.g. price (= amount), can be easily calculated from the other two.

                                 

                                Regards

                                • 13. Re: Trigger not working.
                                  Praveen Kumar Singh

                                  With  Respect to every one i am not an EXPERT On Oracle...i am just trying to lean and it hardly 15 days i have spent on this...i know there are some serious issues in my design which i will overcome with all the suggestion given by every one....please suggest me any good Book through which i can increase my theory knowledge. i am trying to learn through piratical on online but this confuses me more as everyone speak through his experience.

                                  • 14. Re: Trigger not working.
                                    Zlatko Sirotic

                                    These are excellent books, which cover different areas:

                                     

                                     

                                    An Introduction to Database Systems (8th Edition) by C.J. Date

                                    Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions by Thomas Kyte

                                    Oracle PL/SQL Programming (5th or 6th Edition) by Steven Feuerstein

                                    Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars

                                     

                                     

                                    Regards

                                    1 person found this helpful
                                    1 2 Previous Next