CODEWITHSUNDEEP
phpmysql
T. Rex
T. Rex

Reputation: 61

Prevent decimal value to go negative in mysql

To prevent user's balance to ever go negative (besides from the checks on the PHP side) I used the following BEFORE INSERT Mysql Trigger:

BEGIN

    UPDATE `table` SET `user_balance` = user_balance - NEW.amount WHERE `uid` = NEW.id;

    -- Some other inserts etc...

END

I thought as this is a BEFORE INSERT trigger, it would prevent insertion of any new invoice that might make the balance negative but it doesn't.

Even though the STRICT MODE is ON and the user_balance column is UNSIGED DECIMAL, the trigger doesn't stop the insertion and the field is simply turned into 0 if it's negative...

I tried running an UPDATE query manually, but it throws an error as expected, the trigger doesn't act the same.

Edit:

Does any one know why it works fine on a manual UPDATE, but not with the trigger?

Upvotes: 1

Views: 1310

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

You have to check the result of the subtraction before perform the update.

 UPDATE `table` 
 SET `user_balance` = user_balance - NEW.amount 
 WHERE `uid` = NEW.id
   AND user_balance - NEW.amount >= 0

Or better yet get the value first and raise an error :

Throw an error in a MySQL trigger

 SET @user_balance := 0;
 SELECT @user_balance := `user_balance`
 FROM `table` 
 WHERE `uid` = NEW.id;

IF @user_balance - user_balance >= 0 THEN
     UPDATE `table` 
     SET `user_balance` = user_balance - NEW.amount 
     WHERE `uid` = NEW.id
       AND user_balance - NEW.amount >= 0
ELSE 
    set @msg = concat('MyTriggerError: Trying to insert a negative value in trigger: '
                     , cast(@user_balance - NEW.amount as varchar));
    signal sqlstate '45000' set message_text = @msg;
END IF;

Upvotes: 3

Related Questions