CODEWITHSUNDEEP
mysqltriggersdatabase-triggerinsert-updatemysql-error-1442
Frits Nagtegaal
Frits Nagtegaal

Reputation: 61

MYSQL UPDATE Table on INSERT into same table

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...

Upvotes: 2

Views: 493

Answers (2)

Frits Nagtegaal
Frits Nagtegaal

Reputation: 61

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

Upvotes: 0

nbk
nbk

Reputation: 49373

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

Upvotes: 1

Related Questions