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 ?

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

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,....)

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.

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 !!

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.

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

thanks a lot !!