How to Create Trigger that prevents insert if a cumulative value exceeds a certain amount?

Question

I have two tables. Table A contains a total amount. Table B contains a list of the cumulative values

I need a trigger that prevents an insert if:

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

table A:

id   name   total_value
105  test   1000

table B:

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

Please see this [related question](https://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working). and supporting method: https://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger/7189396#7189396 — bigbyte, Mar 31 '16 at 19:46

score 0 · Answer 1 · answered Mar 31 '16 at 20:13

I got it. See elaborate code below:

Turns out it was simpler than expected.

-- 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);

How to Create Trigger that prevents insert if a cumulative value exceeds a certain amount?

1 Answers1