Error 1362 updating of new row is not allowed in after trigger

Введение

Триггер — это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных.

События к которым можно привязывать триггеры в MySQL: INSERT, UPDATE, DELETE.

Время, в которое будет исполнен триггер может быть: BEFORE (до наступления заданного события) или AFTER (после него).

Ограничения (не все)

Во-первых, триггеры появились в MySQL 5.0.2.

Во-вторых, триггеры в MySQL могут создаваться только пользователем с привилегией SUPER, т.е. если Ваше приложение использует триггеры, то ни на одном, даже платном, хостинге оно работать не будет.

Во-третьих, нельзя редактировать таблицу уже открытую для чтения или записи условием инициализации триггера. В этом случае просто используйте оператор SET для нужных полей.

CREATE TRIGGER `artists_litera_autoupdate` AFTER UPDATE ON `cms_artists`
   FOR EACH ROW BEGIN
      UPDATE `cms_artists` SET `litera` = UPPER(SUBSTRING(NEW.`name`, 1, 1)) WHERE `id` = OLD.`id`;
   END;

В противном случае Вы получите такую ошибку:

#1442 — Can’t update table ‘tablename’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Кроме того в показанном выше запросе есть ещё одна ошибка, которая после исправления предидущей обязательно появится:

ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

В-четвёртых, значения надо менять до обновления.

В конечном счёте правильный вариант:

CREATE TRIGGER `artists_litera_autoupdate` BEFORE UPDATE ON `cms_artists`
 FOR EACH ROW BEGIN
    SET NEW.`litera`= UPPER(SUBSTRING(NEW.`name`, 1, 1));
 END;

В-пятых, при очищении таблицы (TRUNCATE) триггер, повешенный на событие DELETE, исполняться не будет.

Синтаксис

CREATE TRIGGER — даём понять MySQL’ю что мы от него хотим.

`artists_litera_autoupdate` — имя создаваемого триггера.

BEFORE — время его исполнения.

UPDATE — событие, по наступлению которого, в указанное время, исполняется триггер.

ON `cms_artists` — таблица, к которой привязывается триггер.

FOR EACH ROW BEGIN — определяет выражения (до END;), которые будут применены к каждому ряду затронутому событием, к которому привязан триггер. Т.е. при запросе:

UPDATE `news` SET `views` = `views` + 1 WHERE `id` IN (1,2,3)

Триггер, повешенный на обновление этой таблицы будет выполнен три раза, если количество записей, удовлетворяющих WHERE равно трём. Тоже самое верно и для множественных вставок:

INSERT INTO `posts` (`date`, `title`) VALUES (NOW(), 'Проверка раз'), (NOW(), 'Проверка два')

В этом случае триггер, повешенный на INSERT для таблицы `posts` будет выполнен два раза.

Пример из жизни

Задача: чтобы максимально упростить SELECT запросы при выводе информации о записях из базы необходимо значение среднего рейтинга и количества голосов хранить в этой же таблице. При этом, как Вы сами прекрасно понимаете, за этими полями надо как-то следить, поддерживать информацию в них в актуальном состоянии.

Как это делается обычно?

Усложенение SELECT’а путём постоянного пересчёта количества голосов из таблицы `cms_videos_ratings` с помощью COUNT(), JOIN и GROUP BY.

Но есть и другой путь — после INSERT’а в таблицу с рейтингом делают дополнительный запрос для получения нового значения среднего рейтинга. Затем поле `rating` в таблицу`videos` обновляют на новое значение, а поле `votes` = `votes` + 1. А ведь есть ещё и удаление рейтинга и его обновление, что добавит не одну лишнюю строчку кода в Ваше приложение.
В этом случае происходит усложнение бизнес-логики приложения а так же создаётся лишняя нагрузка. Кто-то, конечно же, скажет, что это экономия на спичках, и опровергнуть я это не смогу, т.к. у меня нет никаких статистических данных а получать и анализировать мне просто некогда.

Давайте просто вернёмся к началу статьи, к определению триггеров. Поскольку это комплириуемая SQL-процедура, то она будет исполняться быстрее интерпретируемого SQL-запроса, который до исполнения ещё пройдёт через Вашу обёртку для работы с базами данных (если таковая имеется), а затем через обёртку для работы с MySQL из php (например).

Зачем вообще об этом задумываться, если это можно поручить MySQL?

DELIMITER $$
CREATE TRIGGER `videos_rating_autoupdate` AFTER INSERT ON `cms_videos_ratings`
    FOR EACH ROW BEGIN
        SET @new_vid = NEW.`video_id`;
        SET @total_votes = (SELECT COUNT(`id`) FROM `cms_videos_ratings` WHERE `video_id` = @new_vid);
        SET @current_rating = (SELECT SUM(`rating`) / @total_votes FROM `cms_videos_ratings` WHERE `video_id` = @new_vid);
        UPDATE `cms_videos` SET `votes` = @total_votes, `rating` = @current_rating WHERE `id` = @new_vid;
    END;
$$

Аналогичное применение триггерам можно найти и для подсчета количества материалов, добавленных пользователем на ресурс и для подсчёта количества его комментариев, элементов в избранном и многих-многих других вещей.

При использовании метода в лоб Ваше приложение покроется десятками лишних строк и здесь уже плевать на производительность — значительно усложняется восприятие Вашего кода.

Важно знать

Как получить список всех триггеров существующих в текущей базе данных?

SHOW TRIGGERS;

Как удалить триггер из текущей базы данных?

DROP TRIGGER `name`;

После удаления таблицы, к которой привязан триггер, не стоит пытаться удалить его (триггер) — MySQL сделает это автоматически.

У меня не создаётся триггер, я использую phpMyAdmin

Сам столкнулся с такой проблемой. Искать причину её появления не было времени, поэтому рекомендую создавать через консоль:

root@Ubuntu-804:~# mysql -uПОЛЬЗОВАТЕЛЬ -pПАРОЛЬ ИМЯ_БАЗЫ_ДАННЫХ
mysql> DELIMITER $$
mysql> CREATE TRIGGER .....
    -> .....
    -> $$

MySQL.com

• синтаксис
• ограничения

Первый пост здесь. Кросспост из моего блога.

bugreport.txt

Steps to reproduce this issue

Firstly, THANK YOU for your efforts on this excellent program, it is very valuable.

1. Create a new BEFORE UPDATE trigger on atable1

CREATE TRIGGER atable1_before_update BEFORE UPDATE ON atable1 FOR EACH ROW BEGIN
set new.country = trim(old.country); END

1. Save it. OK.
2. Change it to AFTER Update
3. Attempt Save your change

Result, (as expected): Warning, sql error 1362: Updating of NEW row is not allowed in AFTER trigger..

1. Attempt to Discard your changes to revert to previous Save trigger

Result: Error Box (Bug report Below)

Hit Continue

1. Note that HeidiSql has cleared the statement, substituting the default Create code

CREATE TRIGGER atable1_before_update BEFORE INSERT ON acategory FOR EACH ROW BEGIN END

1. Note that Saving now will save the default CREATE code, and you have not discarded changes, you have effectively dropped your trigger and replaced it with default.

2. Perform steps 1 — 6, but don’t save. Instead try to click on the database window.

Result: Confirm msgbox: do you want to save modified trigger?

Yes: it will save the default trigger

No: Dismisses Confirm Box

1. Refresh you database.

NOTE THAT TRIGGER HAS BEEN DROPPED

Current behavior

In Triggers Editor, Errors and won’t revert to your last saved trigger on hitting «Discard». In some circumstances drops previously saved trigger unexpectedly, and crashes to desktop.

Expected behavior

Should not error or crash, and should revert to your last saved trigger, should not drop previously saved trigger

Possible solution

Environment

• HeidiSQL version:
  9.5.0.5449

• Database system and version:

• Operating system:

Business scenario: balance shared by different business systems, hjmallind_user and ims_cjdc_user two tables have different balance fields, but share the balance value.

Trigger definition:

DROP TRIGGER IF EXISTS `test-up_ds_wallet`;
CREATE  TRIGGER `test-up_ds_wallet` AFTER UPDATE ON `ims_cjdc_user` 
    FOR EACH ROW 
    BEGIN
        DECLARE ds_money decimal(10,2);
        IF new.wallet <> old.wallet THEN
            select money into ds_money from hjmallind_user where ptuserid=new.id;

            #Resolve trigger deadlock
            IF ds_money <> new.wallet THEN
                UPDATE hjmallind_user set money=new.wallet where ptuserid=new.id;
            END IF ;
      END IF ;
    END;

DROP TRIGGER IF EXISTS `test-up_wm_wallet`;
CREATE  TRIGGER `test-up_wm_wallet` AFTER UPDATE ON `hjmallind_user` 
    FOR EACH ROW 
    BEGIN
        DECLARE wm_wallet decimal(10,2);
        IF new.money <> old.money THEN
            select wallet into wm_wallet from ims_cjdc_user where id=new.ptuserid;

            #Resolve trigger deadlock
            IF wm_wallet <> new.money THEN
                UPDATE ims_cjdc_user set wallet=new.money where id=new.ptuserid;
            END IF ;
        END IF ;
    END;

Check code

select id,wallet from ims_cjdc_user where id=164438;
select id,ptuserid,money from hjmallind_user where ptuserid=164438;

-- update hjmallind_user set money=money+50 where ptuserid=8426;
update ims_cjdc_user set wallet=wallet+20.50 where id=164438;

select id,wallet from ims_cjdc_user where id=164438;
select id,ptuserid,money from hjmallind_user where ptuserid=164438;

Understanding of database triggers new and old

The values before and after updating are often used in database triggers. It is important to understand the role of new and old. At that time, I had a situation like this: I want to insert a row of data, get a unit price in other tables in the row, then multiply the data in this row to the total amount, and replace the amount in this row with the result of multiplication.

First I use after, and then change my value.

In Oracle, the lines before and after execution are represented by: old and: new. In MySQL, old and new are used to represent the data before and after execution.

The origin of the problem

The previous trigger for the database was written like this,

1 CREATE TRIGGER triggerName after insert ON consumeinfo
2     FOR EACH ROW
3     BEGIN
 4. Update consumeinfo set new. Amount = 0;
5     END;

Trigger creation is OK, but the following error occurred when inserting data.

[Err] 1442 - Can't update table 'consumeinfo' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

However, according to the results of online search, you can modify this table without using update consumeinfo and directly use SET new. Amount = 0. This is right, because if you use new to change the current amount and then save it to the database, you don’t need to use update consumeinfo.

After some efforts, the following is the successful code. Post it and have a look

CREATE TRIGGER addnewReco BEFORE INSERT ON consumeinfo FOR EACH ROW
BEGIN
 SET new. Amount =(
    SELECT ` unit price`
    FROM pricenow
    WHERE ` type '= new
    )* new. Quantity;
END;

Later, when I was eating and drinking soup, I suddenly thought that new and old were used differently on after and before. In fact, it’s because new can’t be assigned after, but can only be read. Copy needs to be assigned before.

Usage of new and old

Let’s talk about the use of old and new. When assigning a value to new, it can only be used in the trigger before and cannot be used in after, for example (the following is correct).

CREATE TRIGGER updateprice
BEFORE insert
ON consumeinfo
FOR EACH ROW
BEGIN
   set new. Amount = 0;
END;

This indicates that before is used to update the current inserted data before it is inserted into the database. In the after trigger, the assignment of new has ended and only the content can be read. If you use after, you can’t use new assignment, you can only take values, otherwise an error will occur, such as

1 CREATE TRIGGER updateprice
2 AFTER insert
3 ON consumeinfo
4 FOR EACH ROW
5 BEGIN
 6. Set new. Amount = 0;
7 END;

The following error occurred:

[Err] 1362 - Updating of NEW row is not allowed in after trigger

Summary: new is assigned a value in the before trigger; Value in after trigger. old is used for value selection? Because assignment doesn’t make sense?