MySQL триггер устанавливает значения для новой строки и обновляет другую в той же таблице

У меня есть таблица, в которой я отслеживаю сборы за конкретный товар. Эти сборы могут меняться со временем, поэтому у меня есть два столбца (startDate, endDate) с текущим набором сборов, которые всегда имеют endDate в далеком будущем. У меня уже есть триггер, который я использую, чтобы выполнить некоторые вычисления для новой вводимой строки, но я также хочу, чтобы, если я ввожу элемент, у которого уже есть запись, я хочу установить конечную дату предыдущей записи на день перед новой записью startDate и новой endDate до заданной удаленной даты. Вот код для того, что я попробовал первым:

CREATE
DEFINER=`root`@`%`
TRIGGER `im`.`splitBeforeIns`
BEFORE INSERT ON `im`.`split`
FOR EACH ROW
BEGIN
    SET NEW.tcPercent = (NEW.tcOfficeFee / NEW.globalFee) * 100 , NEW.proPercent = 100 - NEW.tcPercent, NEW.endDate = 20501231;
    UPDATE im.split set endDate = ADDDATE(NEW.startDate, -1) where procKey = NEW.procKey AND endDate = 20501231;
END$

Я получаю ошибку:

ERROR 1442: Can't update table 'split' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
 donbyte28 июн. 2012 г., 18:54
@RolandoMySQLDBA Я не уверен, как это помогает. , , согласно ответам ниже, невозможно обновить другую строку в той же таблице с помощью триггера. Как вы думаете, это возможно?
 Brian Driscoll28 июн. 2012 г., 17:13
Попробуйте выполнить обновление в триггере AFTER INSERT.
 RolandoMySQLDBA28 июн. 2012 г., 17:56
Пожалуйста, покажите следующее: 1)SHOW CREATE TABLE im.split\G и 2) образец команды INSERT вim.split.
 Brian Driscoll28 июн. 2012 г., 17:17
Дело в том, что вы не можете явно обновить цель своего триггера изнутри триггера. Возможно, вам придется найти другой способ выполнить свою задачу.
 donbyte28 июн. 2012 г., 17:15
@BrianDriscoll Я попробовал это :-( Я получил ту же ошибку.

Ответы на вопрос(4)

Мне удалось заставить его работать, создав «таблицу-обертку». который определяется теми же полями, что и ваша целевая таблица, но с использованием механизма хранения FEDERATED. Сервер федерации, который я определил для целевого сервера / самого mysql, поэтому на "localhost". Триггер, который я затем сделал, изменил строку в таблице-оболочке. Конечно, вы должны быть очень уверены, что вы делаете в триггере, чтобы избежать рекурсивного цикла. Также потенциально производительность не настолько хороша; Я не тестировал производительность, но она работала годами в производственной среде.

Если у вас определен UNIQUE KEY (procKey, EndDate), то, возможно, вы можете удалить вторую строку триггера. Также удалите жестко запрограммированную дату из триггера.

CREATE
DEFINER=`root`@`%`
TRIGGER `im`.`splitBeforeIns`
BEFORE INSERT ON `im`.`split`
FOR EACH ROW
BEGIN
    SET NEW.tcPercent = (NEW.tcOfficeFee / NEW.globalFee) * 100 , NEW.proPercent = 100 - NEW.tcPercent;
END$

и сделатьINSERT ON DUPLICATE KEY UPDATE как это:

INSERT INTO im.split ...
ON DUPLICATE KEY UPDATE
endDate = ADDDATE(startDate, -1);

Вы также можете определить endDate в im.split следующим образом

enddate DATE DEFAULT '2050-12-31'

ОтMySQL Docs:

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

Вам нужно будет найти другой способ сделать то, что вы пытаетесь сделать.

Решение Вопроса

Ответ на этот вопрос может быть нежелательным, но он таков: вы не можете этого делать.

Триггер не можетupdate another row of the same table как строка, триггер был вызван из.

Типичный способ сделать это - создать хранимую процедуру, которая вставляет в / Updates целевую таблицу, затем обновляет другую строку (и), все в транзакции.

 donbyte28 июн. 2012 г., 18:58
Спасибо, что предложили мне путь к достижению цели.

Ваш ответ на вопрос