Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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 ;
share|improve this answer
    
Sir is it possible to display that error message on user page using any mechanism ? Or can we generate any mechanism that ll provide some notification to user that stock is low now. –  user3145373 ツ Jan 7 at 12:57
    
I hope you can call function from your code and assign result to some variable and show it to user, I hope it shouldn't be a problem. For example have a look at stackoverflow.com/questions/12255676/… –  Abdul Manaf Jan 7 at 12:59
    
Yes, Please execute it and have a look, The messages will be displayed are "prod_qty is updated successfully" and "prod_qty is less than or equal to ordered qty" –  Abdul Manaf Jan 7 at 13:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.