mysql - 如果累计值超过一定数量，如何创建阻止插入的触发器？

Question

我有两张 table 。表 A 包含总量。表 B 包含累积值的列表

如果出现以下情况，我需要一个阻止插入的触发器:

select sum(value) from table_b where tbl_a_fk = 105 > 
select total_value from table_a where id = 105

表A:

id   name   total_value
105  test   1000

表 B:

id   tbl_a_fk  name   value
1    105       test1  500
2    105       test2  400
3    105       test3  50
...

Answer 1

我明白了。请参阅下面详细的代码:

事实证明它比预期的要简单。

-- Dumping database structure for test
CREATE DATABASE IF NOT EXISTS `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;


-- Dumping structure for table test.table_a
CREATE TABLE IF NOT EXISTS `table_a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


INSERT INTO `table_a` (`id`, `name`, `value`) VALUES
    (105, 'test1', 1000);


CREATE TABLE IF NOT EXISTS `table_b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tbl_a_fk` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_table_b_table_a` (`tbl_a_fk`),
  CONSTRAINT `FK_table_b_table_a` FOREIGN KEY (`tbl_a_fk`) REFERENCES `table_a` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;



INSERT INTO `table_b` (`id`, `tbl_a_fk`, `name`, `value`) VALUES
    (1, 105, 'test1', 400),
    (2, 105, 'test2', 400),
    (3, 105, 'test3', 50),
    (7, 105, 'test4', 100);


DELIMITER //
CREATE TRIGGER `table_b_before_insert` BEFORE INSERT ON `table_b` FOR EACH ROW BEGIN

    SET @_sum = (SELECT sum(value) from table_b where tbl_a_fk=new.tbl_a_fk);
    SET @_units = (SELECT value from table_a where id=new.tbl_a_fk);
    set @msg = "Value Surpassed";

    IF @_sum > @_units THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
    END IF;

END//
DELIMITER ;


insert into table_b values(null,105,'test5',100);