title: MySQL: Calculate value and set when triggered by an INSERT in another table
body: <p>Quick intro: I am trying to make a program that would allow me and my friends to keep track of who ows who money. Right now I am working on the database.
  I am testing it on <a href="https://paiza.io/projects/gmMbC36xAZmPG0bpcuoZiQ?language=mysql" rel="nofollow noreferrer">this website.</a></p>
  <pre><code>CREATE TABLE `payments`
  (
   `payment_id` integer NOT NULL AUTO_INCREMENT ,
   `title`      varchar(200) NOT NULL ,
   `descrp`     tinytext NULL ,
   `room_id`    integer NOT NULL ,
   `u_from`       integer NOT NULL ,
   `u_to`         integer NOT NULL ,
   `value`      decimal DEFAULT 0, CHECK (value&gt;=0),
  
  PRIMARY KEY (`payment_id`),
  KEY `fkIdx_39` (`u_from`),
  CONSTRAINT `FK_38` FOREIGN KEY `fkIdx_39` (`u_from`) REFERENCES `users` (`user_id`),
  KEY `fkIdx_42` (`u_to`),
  CONSTRAINT `FK_41` FOREIGN KEY `fkIdx_42` (`u_to`) REFERENCES `users` (`user_id`),
  KEY `fkIdx_51` (`room_id`),
  CONSTRAINT `FK_50` FOREIGN KEY `fkIdx_51` (`room_id`) REFERENCES `rooms` (`room_id`)
  );
  
  
  CREATE TABLE `rooms`
  (
   `room_id`  integer NOT NULL AUTO_INCREMENT ,
   `web_link` varchar(30) NOT NULL ,
   `name`     varchar(200) NOT NULL ,
   `descrp`   tinytext,
  
  PRIMARY KEY (`room_id`)
  );
  
  
  CREATE TABLE `user_room`
  (
   `id`        integer NOT NULL AUTO_INCREMENT ,
   `user_id`   integer NOT NULL ,
   `room_id`   integer NOT NULL ,
   `user_role` varchar(200) DEFAULT &quot;standard&quot; ,
   `cash`      decimal NOT NULL DEFAULT 0 ,
  
  PRIMARY KEY (`id`),
  KEY `fkIdx_16` (`user_id`),
  CONSTRAINT `FK_15` FOREIGN KEY `fkIdx_16` (`user_id`) REFERENCES `users` (`user_id`),
  KEY `fkIdx_36` (`room_id`),
  CONSTRAINT `FK_35` FOREIGN KEY `fkIdx_36` (`room_id`) REFERENCES `rooms` (`room_id`)
  );
  
  
  CREATE TABLE `users`
  (
   `user_id`      integer NOT NULL AUTO_INCREMENT ,
   `login`        varchar(50) NOT NULL ,
   `display_name` varchar(100) NULL ,
  
  PRIMARY KEY (`user_id`)
  );
  
  
  DELIMITER //
  CREATE TRIGGER after_new_payment_sum_cash
  AFTER INSERT ON payments FOR EACH ROW
  BEGIN
      DECLARE user_from_cash decimal;
      DECLARE user_to_cash   decimal;
      SET @user_from_cash := (SELECT cash FROM user_room WHERE user_id = NEW.u_from);
      SET @user_to_cash   := (SELECT cash FROM user_room WHERE user_id = NEW.u_to);
  
      UPDATE user_room SET cash = (user_from_cash - NEW.value) WHERE user_id = NEW.u_from;
      UPDATE user_room SET cash = (user_to_cash + NEW.value) WHERE user_id = NEW.u_to;
  
  END //
  DELIMITER ;
  
  
  INSERT INTO users     (login, display_name)    VALUES (&quot;kacper1&quot;, &quot;Kacper2&quot;);
  INSERT INTO users     (login, display_name)    VALUES (&quot;kacper2&quot;, &quot;Kacper2&quot;);
  INSERT INTO rooms     (web_link, name, descrp) VALUES ('xx', 'room1', 'description');
  INSERT INTO user_room (user_id, room_id)       VALUES ((SELECT user_id FROM users WHERE login='kacper1'),
                                                               (SELECT room_id FROM rooms WHERE name ='room1'));
  INSERT INTO user_room (user_id, room_id)       VALUES ((SELECT user_id FROM users WHERE login='kacper2'),
                                                               (SELECT room_id FROM rooms WHERE name ='room1'));
  
  select * from users;
  select * from rooms;
  select * from user_room;
  select * from payments;
  
  INSERT INTO payments (title, descrp, room_id, u_from, u_to, value) VALUES ('plat1', 'plat1 desc', 1, 1, 2, 10);
  
  select * from user_room;
  </code></pre>
  <p>As you can see by yourself I get this error: <code>ERROR 1048 (23000) at line 88: Column 'cash' cannot be null</code><br />
  The purpose of the trigger is to calculate the <code>cash</code> filed in <code>user_room</code> table for <code>user_from</code> and <code>user_to</code> and keep it constantly updated.<br />
  As far as I understand this, one of the <code>SET...</code> lines is giving this error.</p>