MySQL 8.0

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

create table orders (
id_buyer int , 
id_product int,
`date` date, 
`status` int );


create table products  (
id int , 
amount  int );

insert into products values 
(8,15),
(9,20);


INSERT INTO orders(id_buyer, id_product, date, status) 
VALUES (2, 8, '2022-05-22', 1),
VALUES (3, 9, '2022-05-22', 2);
;







select * from orders;
select * from products;


id
amount


8
15

9
20


CREATE TRIGGER after_orders_insert AFTER INSERT ON orders 
FOR EACH ROW
  UPDATE products
     SET amount=amount - 1
   WHERE id = NEW.id_product
   AND NEW.status =1;
INSERT INTO orders(id_buyer, id_product, date, status) 
VALUES (2, 8, '2022-05-22', 1);
select * from products;


id
amount


8
14

9
20