Mysql – How to call stored procedure inside trigger

MySQLstored-procedurestrigger

I have made stored procedure that will update product quantity as per product id.

That works fine for me.

Following is code :

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updatequantity`(IN id int, IN qty int)
BEGIN
    update product_master 
    set prod_qty = (prod_qty - qty)
    where prod_id = id;
END

it works greatly for me. Now I want to put trigger on product_master table that will first check that enough quantity is there or not in stock ?

Suppose order quantity is 100 and total available product quantity is 50 then procedure should not be called instead some error message should be displayed.
If enough quantity is there then only stored procedure should be called.

Anyone have idea that how to proceed for that ?

Thank You.

Best Answer

Instead of creating trigger you can use below code, It will also return you success or failure message.

DELIMITER ;
DROP FUNCTION IF EXISTS to_updatequantity;

DELIMITER //
CREATE FUNCTION to_updatequantity(id int, qty int) RETURNS CHAR(100) DETERMINISTIC
BEGIN

    SELECT prod_qty INTO @var_prod_qty FROM product_master WHERE prod_id = id;

    IF (@var_prod_qty > qty) THEN
        UPDATE product_master SET prod_qty = prod_qty - qty WHERE prod_id = id;
        SET @msg = 'prod_qty is updated successfully';
    ELSE 
        SET @msg = 'prod_qty is less than or equal to ordered qty'; 
    END IF;  

RETURN @msg;
END //
DELIMITER ;
Related Question