Checks in MySQL triggers

Question

MySQL

There was a need to check a certain value before adding/updating it in the database.
There is the following table:

CREATE TABLE `balance` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `item_id` INT(10) NOT NULL DEFAULT '0',
  `place_id` INT(10) NOT NULL DEFAULT '0',
  `count` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `i_pl_itm` (`item_id`, `place_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM

And trigger:

BEGIN
  SET @item_id := NEW.`item_id`; 
  SET @place_id := NEW.`place_id`; 
  SET @item_count := NEW.`count`;
  INSERT INTO `balance` (`item_id`,`place_id`,`count`) 
   	VALUES (@item_id,@place_id,@item_count)  
  ON DUPLICATE KEY UPDATE `count`=`count` + @item_count;
END

Here is a table that takes into account the receipt / expenditure of goods:

CREATE TABLE `variation` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `person_id` INT(11) NOT NULL DEFAULT '0',
  `place_id` INT(11) NOT NULL DEFAULT '0',
  `item_id` INT(10) NOT NULL DEFAULT '0',
  `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `item_count` INT(11) NOT NULL DEFAULT '0',
  `summ` DOUBLE(10,2) UNSIGNED NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  INDEX `i_date` (`date`),
  INDEX `i_item` (`item_id`),
  INDEX `i_place` (`place_id`),
  INDEX `i_person` (`person_id`),
  INDEX `i_dt_pl` (`date`, `place_id`),
  INDEX `i_dt_pn` (`date`, `person_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM

On this table the trigger on AFTER INSERT is hung up.

The bottom line is this: you can’t sell more goods than you have in stock.
So the question arose, how would it be better to check the count value before adding it to the database: in a script or in a trigger?
It would be more convenient to do, of course, a check in the script.
But, anyway, it would be desirable to know, how in the trigger this business to write down? How to "exit" a trigger in case of an error or certain conditions? As far as I know, the trigger should in any way be completely executed?

ps I have no errors in the trigger? :)

pps or is it better to hang up the trigger on BEFORE INSERT in the balance table ?

Answer 1

IF (statement) THEN… ENDIF

Answer 2

Dig towards transactions, stored procedures and select for update.
And no triggers.

Answer 3

Slightly updated the question.
The trigger wrote:

BEGIN 
  SET @real_count := (SELECT `count` FROM `balance` WHERE
    `item_id` = NEW.`item_id` AND
    `place_id` = NEW.`place_id`);	
  IF (@real_count >= NEW.`item_count`) THEN
    SET @item_id := NEW.`item_id`; 
    SET @place_id := NEW.`place_id`; 
    SET @item_count := NEW.`item_count`;
    INSERT INTO `balance` (`item_id`,`place_id`,`count`) 
     	VALUES (@item_id,@place_id,@item_count)  
    ON DUPLICATE KEY UPDATE `count`=`count` + @item_count;
  END IF;
END

That is, now if there is already a sufficient amount of goods, then the corresponding instructions will be executed. Otherwise, nothing will simply be added ... or ... damn it. I'd better go and drink coffee ... I'll think ...