MariaDBのCREATE TABLEとストアドプロシージャとトリガーと。

サマリー用のテーブル定義

CREATE TABLE `cart_summary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `goods_id` int(10) unsigned NOT NULL,
  `cnt` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix01_cart_summary` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  ;

プロシージャ

DELIMITER ;;
CREATE PROCEDURE `cart_summary`(
  IN _act char(1),
  IN _goods_id int,
  OUT _result int
)
BEGIN

    DECLARE _valid int ;

    SET _result = 0;

    BEGIN
      DECLARE _not_found TINYINT UNSIGNED DEFAULT 0;
      DECLARE _tmp_goods_id INT UNSIGNED;

      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
        SET _result = -99;
      END;

/* goods_idとvariatiom_numberで検索かけて、レコードがなければInsertする。 */
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;
      SELECT goods_id FROM cart_summary WHERE goods_id = _goods_id AND variatiom_number = _variatiom_number;
      IF _not_found=1 THEN
            INSERT INTO cart.cart_summary(goods_id,variatiom_number,cnt) VALUES (_goods_id, _variatiom_number, 0);
      ELSE
            SET _result = -10 ;
      END IF;

       CASE _act
         WHEN '+' THEN
           UPDATE cart_summary
           SET 
             cnt = cnt + 1
           WHERE 
             goods_id = _goods_id AND variatiom_number = _variatiom_number;
         SET _result = 0 ;

         WHEN '-' THEN
           UPDATE cart_summary
           SET
             cnt = cnt - 1
           WHERE 
             goods_id = _goods_id AND variatiom_number = _variatiom_number AND cnt > 0 ;
         SET _result = 0 ;
        END CASE ;
    END ;
END ;;

DELIMITER ;

トリガー。

DELIMITER ;;

CREATE TRIGGER cart.cart_unit_ins AFTER INSERT ON cart.cart_unit FOR EACH ROW
BEGIN 
  call cart_summary('+', NEW.GOODS_ID, NEW.VARIATION_NUMBER, @r) ;
END ;;

DELIMITER ;


NEW.GOODS_ID, NEW.VARIATION_NUMBER


DELIMITER ;;

CREATE TRIGGER cart.cart_unit_del AFTER DELETE ON cart.cart_unit FOR EACH ROW
BEGIN 
  call cart_summary('-', OLD.GOODS_ID, OLD.VARIATION_NUMBER, @r) ;
END ;;

DELIMITER ;