How do I update a table inside an INSERT trigger?

Question

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 ;

i think you need to use BEFORE INSERT and SET like said on this https://stackoverflow.com/questions/15300673/mysql-error-cant-update-table-in-stored-function-trigger-because-it-is-already — Ondrelat, Mar 05 '22 at 15:54

score 0 · Answer 1 · answered Mar 05 '22 at 16:10

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 and SELECT 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

score 0 · Answer 2 · edited Mar 06 '22 at 03:50

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 ;

How do I update a table inside an INSERT trigger?

2 Answers2