MYSQL UPDATE Table on INSERT on table

Question

这看起来很简单，但仍然是一个挑战。我尽可能地简化了我的问题。

我有一个包含一条记录的test_table：

    id | cost_per_record
    1  | 24

在INSERT之后，我希望表看起来像这样：

    id | cost_per_record
    1  | 12
    2  | 12

在我工作的应用程序中，我不能调用存储过程，所以我使用的代码包括：

    DROP TABLE IF EXISTS `test_table`;
    CREATE TABLE `test_table` (
      `id` int(11) NOT NULL,
      `cost_per_record` int(11) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    INSERT INTO `test_table` (`id`, `cost_per_record`) VALUES (1,24);

    DELIMITER $$
    CREATE TRIGGER `test_insert` BEFORE INSERT ON `test_table` FOR EACH ROW 
    BEGIN
    update `test_table` set `cost_per_record` = 12 
    where `id`  = 1;
    END
    $$
    DELIMITER ;

    INSERT INTO `test_table` (`id`, `cost_per_record`) VALUES
    (2,12);

我通常收到的错误(在其他尝试中也是如此)：

    MySQL said: Documentation 

    #1442 - Can't update table 'kan_test_update' in stored function/trigger because it is already      used by statement which invoked this stored function/trigger

相信我，我在这个论坛上读到了相当多的答案，还在博客上看到有人说这是impossible。但我(仍然)不接受这一点。所以..。任何解决方案...谢谢..。

Answer 1

像下面这样的触发器只能更改当前插入的行。

DELIMITER $$
CREATE TRIGGER `test_insert` BEFORE INSERT ON `test_table` FOR EACH ROW 
BEGIN
IF NEW.`id`  = 1 THEN
    SET NEW.`cost_per_record` = 12;
END IF;
END
$$
DELIMITER ;

要更新其他行并插入，只能使用存储过程。

喜欢

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`( IN _id Integer, IN _Value INTEGER)
BEGIN
IF _id  >  1 THEN
    UPDATE test_table SET `cost_per_record` = 12 WHERE id = 1;
END IF;
     INSERT INTO `test_table` (`id`, `cost_per_record`) VALUES
    (_id,_value);
END$$
DELIMITER ;

并使用

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL PRIMARY KEY,
  `cost_per_record` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test_table` (`id`, `cost_per_record`) VALUES (1,24);


call new_procedure(2,12);

SELECT * FROM test_table;

插入新行，或者您发现另一个算法可以使用触发器，因此不涉及同一个表

Triggers有很多限制，所以当您尝试创建一个新系统时，请记住这一点

Answer 2

对于我来说，下面的解决方案是可行的，所以使用第二个表。我添加了一些智能，因此添加第三个记录将从12个-> 8降低成本。我使用这一点来为从同一个meal_group订购更多食物的人提供折扣。这仍然是一种变通方法，而不是最初的问题，但是嘿。感谢所有人，谁回复了。

DROP TABLE IF EXISTS `test_table`;
DROP TABLE IF EXISTS `sub_table`;
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `sub_table` (
 `id` int(11) NOT NULL,
 `cost_per_record` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test_table` (`id`) VALUES (1);
INSERT INTO `sub_table` (`id`, `cost_per_record`) VALUES (1,24);

DELIMITER $$
CREATE TRIGGER `test_insert` AFTER INSERT ON `test_table` FOR EACH ROW 
BEGIN
UPDATE `sub_table` set `cost_per_record` = (24/(SELECT COUNT(*) FROM `test_table`)) ;
INSERT INTO `sub_table` (`id`, `cost_per_record`) VALUES (NEW.ID,(24/(SELECT COUNT(*) FROM `test_table`)));
END
$$
DELIMITER ;

INSERT INTO `test_table` (`id`) VALUES (2);
INSERT INTO `test_table` (`id`) VALUES (3);
SELECT * from `sub_table`;