0

i have a mysql products table like that;

http://www.sqlfiddle.com/#!9/56ac8e/2

when each line is added, I want it to compare with the latest price of the same coded product. if it is cheaper than the latest price, I want it to add rows to another table in the following way.

discount table is ;

http://www.sqlfiddle.com/#!9/c2227d/1

i changed the codes in the form of the link below, but it didn't work. Compare rows in same table in mysql

CREATE DEFINER = CURRENT_USER TRIGGER `database`.`products_AFTER_INSERT` AFTER INSERT ON `products` FOR EACH ROW
BEGIN
        WHERE NEW.product_id = OLD.product_id
        IF NEW.price < OLD.price
        THEN
                INSERT INTO discount
                    (
                        id (auto) ,
                        product_id,
                        name     ,
                        old.price    ,
                        new.price      ,
                        discount ((old.price / new.price )/10)
                    );
        END IF;
    END$$

can you help me in this regard ?

acsm
  • 1
  • 3
  • [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055) and [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Akina Sep 28 '22 at 12:24
  • OLD values does not exist in INSERT triggers. WHERE clause (and any other condition expression) cannot be a separate statement. Used INSERT INTO syntax is not correct. – Akina Sep 28 '22 at 12:27
  • i am sorry picture upload. but table codes is not work . i was try like that | left | center | right | |:---- |:------:| -----:| | One | Two | Three | – acsm Sep 28 '22 at 12:52
  • First link, "5. Some Additional Suggestions" and "C. Consider providing an SQL Fiddle or DB Fiddle". – Akina Sep 28 '22 at 13:04
  • thank you for the advice. i rearranged my question using sqlfiddle. i hope I can find an answer that will solve my problem – acsm Sep 28 '22 at 13:33
  • https://dbfiddle.uk/ujxHOghX – Akina Sep 28 '22 at 13:58
  • thank you for the response. but there are two problems. the id values in the discount table are null. secondly, trigger should only add to the discount table if the new price is lower than the previous price. in this way, it adds to each data entry. – acsm Sep 28 '22 at 14:55
  • Does your tables **really** uses `varchar(300)` for all columns datatype? – Akina Sep 28 '22 at 15:33
  • i don't really need that many characters. only the url and name can be long. – acsm Sep 29 '22 at 09:17
  • trigger should only add to the discount table if the new price is lower than the previous price. in this way, it adds to each data entry – acsm Sep 29 '22 at 09:17
  • varchar update - - Products table - http://www.sqlfiddle.com/#!9/3830b2/1 discount table - http://www.sqlfiddle.com/#!9/4e190d3/1 – acsm Sep 29 '22 at 09:23
  • I mean: do you really store numbers as strings with non-standard decimal separator? do you really store datetime as string with non-standard datetime format? This everything should produce many excess convertions in your code. – Akina Sep 29 '22 at 09:34
  • I'm very new at this. I set up this way. however, if you have a suggestion to be more stable, I can revise it. – acsm Sep 29 '22 at 09:47
  • https://dbfiddle.uk/fbeR9LTE – Akina Sep 29 '22 at 10:15
  • thank you so much for your help. you are quite knowledgeable about this. finally, can you write me the code to send a message to telegram for each record added to the discount table? as in this link https://www.sqlekibi.com/sql-server/sql-server-ile-telegrama-bildirim-gonderin.html/ – acsm Sep 30 '22 at 08:44

1 Answers1

0

Solved. Akina Thank You..

https://dbfiddle.uk/fbeR9LTE

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id CHAR(7),
    name VARCHAR(255),
    price DECIMAL(10, 2),
    `date` DATETIME,
    url VARCHAR(768)
    );

CREATE TABLE discount (
    product_id CHAR(7) PRIMARY KEY,
    name varchar(255),
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    discount DECIMAL(3, 2),
    url VARCHAR(768)
    );


CREATE TRIGGER tr_bi_discount
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
  DECLARE prev_price DECIMAL(10, 2);
  SELECT price INTO prev_price FROM products WHERE product_id = NEW.product_id ORDER BY `date` DESC LIMIT 1;
  IF NEW.price < prev_price THEN 
    REPLACE INTO discount 
      SELECT NEW.product_id, NEW.name, prev_price, NEW.price, 1 - NEW.price / prev_price, NEW.url;
  END IF;
END



INSERT INTO products (product_id, name, price, `date`, url) VALUES
('AD12CDS', 'iphone 13',   790.00, '2022-09-13 15:05', 'https://www.abcde.com.tr/1'),
('DDSC22S', 'samsung s22', 989.00, '2022-09-14 15:05', 'https://www.abcde.com.tr/2'),
('SDX10XA', 'xioami a10',  540.00, '2022-09-15 15:05', 'https://www.abcde.com.tr/3'),
('SDX10XA', 'xioami a10',  539.00, '2022-09-16 15:05', 'https://www.abcde.com.tr/4'),
('DDSC22S', 'samsung s22', 990.00, '2022-09-17 15:05', 'https://www.abcde.com.tr/5'),
('DDSC22S', 'samsung s22', 800.00, '2022-09-18 15:05', 'https://www.abcde.com.tr/6'),
('AD12CDS', 'iphone 13',   800.00, '2022-09-19 15:05', 'https://www.abcde.com.tr/7'),
('AD12CDS', 'iphone 13',   600.00, '2022-09-20 15:05', 'https://www.abcde.com.tr/8'),
('AA11SDE', 'ipnone 12',   500.00, '2022-09-21 15:05', 'https://www.abcde.com.tr/9'),
('DFR12SD', 'samsung s20', 400.00, '2022-09-22 15:05', 'https://www.abcde.com.tr/10');
SELECT * FROM products;
SELECT * FROM discount;
acsm
  • 1
  • 3