0

I was answered by @Dharman for my problem (plz click here-> How to update stock quantity according to their batch numbers. And his suggested code is working for me.

Now, I just want to INSERT the updated rows into "temp_sales". Here is sample picture of required "temp_sales" https://i.stack.imgur.com/ab1an.png.
Thanks

Sohail Ahmad
  • 15
  • 7
  • Why do you need to copy data from one table to another? But anyway...investigate `INSERT...SELECT` queries – ADyson Mar 22 '21 at 08:51
  • Actually I want to keep track of profits on same product which was purchased on different prices (according to their batch numbers) and sold on same price. i.e I purchased 1 Coke bottle at pur_price of 5,6,7 or 8 and sale price was 10. How can I keep track of profits? – Sohail Ahmad Mar 22 '21 at 09:05
  • Well don't you already keep a record of what price you purchased things at? – ADyson Mar 22 '21 at 09:09
  • Surely yes, but using no good method. But this time I am working to keep track of inventory by their batch numbers because it will help me to calculate batch-wise profits of each product. – Sohail Ahmad Mar 22 '21 at 13:15

1 Answers1

1

You just need to create the trigger like this :

CREATE TRIGGER insert_updated_data BEFORE UPDATE ON temp_sales
FOR EACH ROW BEGIN
INSERT INTO temp_sales2 (product_code, batch_number, qty, pur_price, price) VALUES (new.product_code, new.batch_number, new.qty, new.pur_price, new.price);
END

Enjoy... :)

wiwek chauhan
  • 468
  • 1
  • 5
  • 12
  • hello wiwek jannat on using your suggested trigger I am getting the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 – Sohail Ahmad Mar 23 '21 at 11:18
  • Can you please give me the trigger SQL code that you have been used for that? – wiwek chauhan Mar 23 '21 at 12:10
  • CREATE TRIGGER insert_updated_data BEFORE UPDATE ON purchase_order FOR EACH ROW BEGIN INSERT INTO `employees_audit` SET action = new.batch_number, employeeNumber = new.unitPrice, lastname = new.qty_avbl, changedat = new.salePrice MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 9 – Sohail Ahmad Mar 23 '21 at 13:20
  • You don't need to use SET keyword in insert query. Your query is wrong man. Try this: INSERT INTO employees_audit (action,employeeNumber,lastname,changedat) VALUES (new.batch_number, new.unitPrice, new.qty_avbl, new.salePrice); – wiwek chauhan Mar 24 '21 at 04:01
  • Its working now. thanks @wiwek jannat – Sohail Ahmad Mar 24 '21 at 09:12