MYSQL UPDATE Table on INSERT into same table

Question

it seems simple, but still a challenge. I simplified my issue as much as possible.

I have this test_table with one record:

    id | cost_per_record
    1  | 24

After an INSERT I want the table to look like this:

    id | cost_per_record
    1  | 12
    2  | 12

From the application I work from I cannot CALL a STORED PROCEDURE so the code I used among others:

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

The error I usually receive (also on other attempts):

    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

Believe me, I read quite some answers on this forum and also ran into blogs saying this is impossible. But I am (still) not accepting this. So.. any solution... thanks...

What happens with 2 or more records to start? Your requirement is somewhat unusual. — Tim Biegeleisen, May 17 '20 at 09:07
'But I am (still) not accepting this' - you should. BTW in a before insert trigger you can adjust the value of the NEW. column. please read https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html for detail. — P.Salmon, May 17 '20 at 09:07
What problem are you trying to solve? Perhaps an alternative design would suit your scenario better — Strawberry, May 17 '20 at 09:10
As said I simplified my problem as much as possible, I just want to update other record(s) from the same table after or before an insert — Frits Nagtegaal, May 17 '20 at 09:22

nbk · Answer 1 · 2020-05-17T13:34:46.940

1

The trigger like following only can change the current inserted row.

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 ;

To update other rows and also insertiung, you can only use a stored procedure.

like

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 ;

and use

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;

To insert new rows Or you find another algorithm that can use triggers and so didn't involve the same table

Triggers have a lot of restrictions, so you should remeber that, when you try to make a new system

edited May 17 '20 at 13:34

answered May 17 '20 at 09:29

nbk

45,398
8
30
47

Thanks, but changing to that code gives this result in my table: id | cost_per_record 1 | 24 2 | 12 – Frits Nagtegaal May 17 '20 at 11:31
bare in mind I add the Trigger after the first insert, the second insert should update the first record – Frits Nagtegaal May 17 '20 at 11:38
@Frits Nagtegaal your last comment is not explained in your question and what's the logic for id 2 updating id one and what's the logic for the amount to set id 1 to? – P.Salmon May 17 '20 at 11:42
@FritsNagtegaal i updated my answer, that is so not solvable, instead use a procedure – nbk May 17 '20 at 12:11
@nbk Thanks, but as stated in the request 'From the application I work from I cannot CALL a STORED PROCEDURE'. I can CALL a SP in the Trigger like you described, but that gives the same 1442 error. I think I need a second table – Frits Nagtegaal May 17 '20 at 13:13
You have to use the procedure instead of the insert and also don't use a trigger see the updated code – nbk May 17 '20 at 13:35

Frits Nagtegaal · Answer 2 · 2020-05-17T13:56:59.083

For me now underneath solution works, so using a second table. I added some intelligence, so adding a third record will reduce the cost from 12 -> 8. I use this to give discount for people ordering more meals from the same meal_group. Still this is a kind of workaround, and not the initial question, but hey. Thanks all, who replied.

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

MYSQL UPDATE Table on INSERT into same table

2 Answers2