MySQL 8.0

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

CREATE TABLE `price_list` (
`price_list_rate` float NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
); 
CREATE TRIGGER tr_bi_check_price_list_rate 
BEFORE INSERT
ON price_list
FOR EACH ROW
BEGIN
IF NEW.price_list_rate <= 0 THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: `price_list`.`price_list_rate` must be positive.';
END IF;
END
CREATE TRIGGER tr_bu_check_price_list_rate 
BEFORE UPDATE
ON price_list
FOR EACH ROW
BEGIN
IF NEW.price_list_rate <= 0 THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: `price_list`.`price_list_rate` must be positive.';
END IF;
END
INSERT INTO price_list (price_list_rate) VALUES (1), (2);

Records: 2  Duplicates: 0  Warnings: 0


INSERT INTO price_list (price_list_rate) VALUES (3), (0);

Error: `price_list`.`price_list_rate` must be positive.


INSERT INTO price_list (price_list_rate) VALUES (4), (-1);

Error: `price_list`.`price_list_rate` must be positive.


UPDATE price_list SET price_list_rate = price_list_rate + 1;

Rows matched: 2  Changed: 2  Warnings: 0


UPDATE price_list SET price_list_rate = price_list_rate - 2;

Error: `price_list`.`price_list_rate` must be positive.


SELECT * FROM price_list;


price_list_rate
created_at
updated_at


2
2023-12-26 01:39:21
2023-12-26 01:39:21

3
2023-12-26 01:39:21
2023-12-26 01:39:21

price_list_rate	created_at	updated_at
2	2023-12-26 01:39:21	2023-12-26 01:39:21
3	2023-12-26 01:39:21	2023-12-26 01:39:21