Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2011
    Posts
    17

    Exclamation Unanswered: SQL trigger help !!



    Following are the tables

    order (orderno,custno,totalcost,dateordered,datedispatch ed,datepaid,status)

    orderline (orderno,prodno,quantity)

    For above tables can anyone show how to write trigger when there is change in quantity in orderline table , totalcost field in order table should gets updated .

    further how can then we write trigger to handle situation when product is removed from stock ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you ask is not possible, because there is no price column

    you've got the worst homework assignments evar!

    please go back to your teach and complain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2011
    Posts
    17
    hi , thanks for looking into query ,

    this is not part of any work, though I wanted to update totalcost when quantity id changed in orderline .

    In this these are additional tables that might be useful

    product (prodno,unitcost,prodname,...)
    customer(custno,custaddr,custname,....)

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Look at UPDATE, INSERT and DELETE triggers. The UPDATE trigger you will need to verify that the quantity and amount have changed and then use the OLD value and subtract from the total and add the NEW value to the total. For DELETE subtract the OLD value from the total and INSERT add NEW value to the total.

    If you still have problems post your trigger and we can help investigate further.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Apr 2011
    Posts
    17
    Hi thanks for the help , i am just new learner if i make mistake please correct it as i am trying whole heartedly to learn triggers


    product (prodno,unitcost,prodname,...)

    customer(custno,custaddr,custname,....)

    order (orderno,custno,totalcost,.......)

    orderline (orderno,prodno,quantity)

    for this I want to write rule/ trigger which update total cost when we make change to quantity

    So following is trigger that I am trying on logical basis, please see

    CREATE OR REPLACE TRIGGER UPDATE_TRIGG
    AFTER UPDATE OF QUANTITY
    ON OREDERLINE
    FOR EACH ROW
    BEGIN
    INSERT INTO ORDER
    VALUES
    (: old.orderno,
    : old.custno ,
    : old.totalcost
    );
    END;
    Please correct if something got wrong in query as I am very new learner !!

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    A question for you? Will there not already be an order record for each of these orderlines? If that is the case then INSERTing a new record is not the correct logic to apply.

    Before looking at the syntax of the trigger lets make sure that we have the correct logic in place.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Apr 2011
    Posts
    17
    can you please suggest any document which can give clear idea of logic and syntax for constructing trigger , it would be really a good help !!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by learnerkid View Post
    can you please suggest any document which can give clear idea of logic and syntax for constructing trigger , it would be really a good help !!
    here you go -- mysql trigger tutorial

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2011
    Posts
    17
    thanks a lot !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •