时间戳

首页 > 解决方案 > MySQL：在另一个表中由 INSERT 触发时计算值并设置

mysql - MySQL：在另一个表中由 INSERT 触发时计算值并设置

问题描述

快速介绍：我正在尝试制作一个程序，让我和我的朋友可以跟踪谁欠谁的钱。现在我正在处理数据库。我正在这个网站上测试它。

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>=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 "standard" ,
 `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 ("kacper1", "Kacper2");
INSERT INTO users     (login, display_name)    VALUES ("kacper2", "Kacper2");
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;

正如您自己看到的那样，我得到了这个错误：ERROR 1048 (23000) at line 88: Column 'cash' cannot be null
触发器的目的是计算表cash中的字段并保持它不断更新。 据我了解，其中一条是给出此错误。user_roomuser_fromuser_to
SET...

标签： mysql

解决方案

公式user_from_cash - NEW.value变为NULL应有的样子@user_from_cash。

进一步改进：

  1. 你最好在触发器中使用局部变量（不是@var）
  2. DECIMAL-datatype 应该定义长度。

推荐阅读