INSERT TRIGGERS SQL. How to retrieve value from another table after insert in new table

1
votes

I am new to SQL triggers. I am currently stuck with the question below.

I have 3 tables -

  1. orders

    orderid int(11) AI PK orderdate date total decimal(8,2)

  2. products

    productid int(11) AI PK productname varchar(50) unit varchar(10) price decimal(8,2)

  3. orderdetails

    orderid int(11) productid int(11) price decimal(8,2)

I have records in both orders and products tables. The orderdetails table is empty, no records.

I want to create an insert trigger on orderdetails to automatically retrieve the price from products table and update it into the orderdetails table whenever i insert a query like;

(e.g. INSERT INTO orderdetails(orderid,productid) values(1,1)) into the orderdetails table.

Any help would be much appreciated.

1
mysqlsqltriggersmysql-workbenchdatabase-trigger

1 Answers

0
votes

I don'thave MySQL at hand but this housld work:

CREATE TRIGGER ins_price BEFORE INSERT ON orderdetails
       FOR EACH ROW
       BEGIN
           SET new.price= (select price from products where productid = new.productid);
       END;//