MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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;

Records: 10  Duplicates: 0  Warnings: 0




id
product_id
name
price
date
url


1
AD12CDS
iphone 13
790.00
2022-09-13 15:05:00
https://www.abcde.com.tr/1

2
DDSC22S
samsung s22
989.00
2022-09-14 15:05:00
https://www.abcde.com.tr/2

3
SDX10XA
xioami a10
540.00
2022-09-15 15:05:00
https://www.abcde.com.tr/3

4
SDX10XA
xioami a10
539.00
2022-09-16 15:05:00
https://www.abcde.com.tr/4

5
DDSC22S
samsung s22
990.00
2022-09-17 15:05:00
https://www.abcde.com.tr/5

6
DDSC22S
samsung s22
800.00
2022-09-18 15:05:00
https://www.abcde.com.tr/6

7
AD12CDS
iphone 13
800.00
2022-09-19 15:05:00
https://www.abcde.com.tr/7

8
AD12CDS
iphone 13
600.00
2022-09-20 15:05:00
https://www.abcde.com.tr/8

9
AA11SDE
ipnone 12
500.00
2022-09-21 15:05:00
https://www.abcde.com.tr/9

10
DFR12SD
samsung s20
400.00
2022-09-22 15:05:00
https://www.abcde.com.tr/10




product_id
name
old_price
new_price
discount
url


AD12CDS
iphone 13
800.00
600.00
0.25
https://www.abcde.com.tr/8

DDSC22S
samsung s22
990.00
800.00
0.19
https://www.abcde.com.tr/6

SDX10XA
xioami a10
540.00
539.00
0.00
https://www.abcde.com.tr/4

id	product_id	name	price	date	url
1	AD12CDS	iphone 13	790.00	2022-09-13 15:05:00	https://www.abcde.com.tr/1
2	DDSC22S	samsung s22	989.00	2022-09-14 15:05:00	https://www.abcde.com.tr/2
3	SDX10XA	xioami a10	540.00	2022-09-15 15:05:00	https://www.abcde.com.tr/3
4	SDX10XA	xioami a10	539.00	2022-09-16 15:05:00	https://www.abcde.com.tr/4
5	DDSC22S	samsung s22	990.00	2022-09-17 15:05:00	https://www.abcde.com.tr/5
6	DDSC22S	samsung s22	800.00	2022-09-18 15:05:00	https://www.abcde.com.tr/6
7	AD12CDS	iphone 13	800.00	2022-09-19 15:05:00	https://www.abcde.com.tr/7
8	AD12CDS	iphone 13	600.00	2022-09-20 15:05:00	https://www.abcde.com.tr/8
9	AA11SDE	ipnone 12	500.00	2022-09-21 15:05:00	https://www.abcde.com.tr/9
10	DFR12SD	samsung s20	400.00	2022-09-22 15:05:00	https://www.abcde.com.tr/10