I'm a newbie to SQL and I have to write a trigger that checks if there is enough product stock for new orders. Also if the order quantity is greater than a certain number I need to update the discount row.
But apparently you are not allowed to update a table from an INSERT trigger. How do I go about doing this?
This is what I've written so far
DELIMITER //
create trigger before_order before insert on order_detail FOR EACH ROW
BEGIN
declare stock_remain int;
declare ord_quantity int;
declare inventory int;
declare price int;
declare p_id int;
declare ord_id int;
select product_id into @p_id from order_detail where product_id = new.product_id;
select unit_price into @price from order_detail where unit_price = new.unit_price;
select order_detail_id into @ord_id from order_detail where order_detail_id = new.order_detail_id;
SET @stock_remain=est_remaining_stock(@price, @p_id);
select quantity into @ord_quantity from order_detail where order_detail_id=@ord_id;
SET @inventory = @stock_remain - @ord_quantity;
IF @inventory < 10 THEN
signal sqlstate '45001' set message_text = "No way ! You cannot do this !";
END IF;
IF @ord_quantity > 40 THEN
UPDATE order_detail SET new.discount = 5 where product_id=new.product_id;
END IF;
END;
//
DELIMITER ;
CodePudding user response:
- Noway to alter the data in the table where the trigger is defined on. But you may alter the data to be saved into the table.
DECLARE variable
andSELECT INTO @variable
uses two different variables.- A bunch of variables can be set on one SELECT INTO statement.
CREATE TRIGGER before_order
BEFORE INSERT ON order_detail
FOR EACH ROW
BEGIN
declare stock_remain int;
declare ord_quantity int;
declare inventory int;
declare price int;
declare p_id int;
declare ord_id int;
select product_id, unit_price, order_detail_id
into p_id, price, ord_id
from order_detail
where product_id = new.product_id;
SET stock_remain=est_remaining_stock(price, p_id);
select quantity
into ord_quantity
from order_detail
where order_detail_id=@ord_id;
SET inventory = stock_remain - ord_quantity;
IF inventory < 10 THEN
signal sqlstate '45001' set message_text = "No way ! You cannot do this !";
END IF;
IF ord_quantity > 40 THEN
SET new.discount = 5;
END IF;
END
CodePudding user response:
you can DECLARE a list of same datatype variables in a single DECLARE statement. I finally commented it, because these variables are not used in the trigger (use of user defined variable starting with @ which is perfectly fine).
you may take into account several rows with the same product id may exists in the new order : reason why I'm proposing to use a SUM on order quantity.
keep in mind the SET new.<column_name> statement in the trigger will be executed FOR EACH ROW inserted so you should not do an UPDATE ... WHERE ...
I may suggest your error message to be more informative about inventory to low to satisfy the order for this product.
DROP TRIGGER IF EXISTS before_order BEFORE INSERT ON order_detail ;
DELIMITER //
CREATE TRIGGER before_order BEFORE INSERT ON order_detail FOR EACH ROW
BEGIN
-- DECLARE stock_remain, ord_quantity, inventory, price, p_id, ord_id INT ;
SET @stock_remain=est_remaining_stock(new.unit_price, new.product_id);
SELECT SUM(quantity) INTO @ord_quantity
FROM order_detail
WHERE order_detail_id = new.order_detail_id
AND product_id = new.product_id ;
SET @inventory := (@stock_remain - @ord_quantity) ;
IF @inventory < 10 THEN
SIGNAL sqlstate '45001' SET message_text := "No way ! You cannot do this !";
END IF;
IF @ord_quantity > 40 THEN
SET new.discount := 5 ;
END IF;
END//
DELIMITER ;