Ошибка 1442 mysql - Исправление ошибок и поиск оптимальных решений проблем

I have the following trigger:

CREATE TRIGGER sum
AFTER INSERT
ON news
FOR EACH ROW
UPDATE news SET NEW.sum = (NEW.int_views + NEW.ext_views)/NEW.pageviews

It sums the int_views and ext_views column of a table and divides them by the total pageviews.

Whenever I try to add a new row to news, I get the following error:

ERROR 1442 (HY000) at line 3: Can't update table 'news' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

The trigger seems pretty simple to me. Is there a reason why the trigger fails to run?

asked Aug 30, 2012 at 19:13

The symptom is, that you are running an UPDATE (for all rows) inside a INSERT trigger — both modify the table, which is not allowed.

That said, if I guess the intention of your trigger correctly, you do not want to update all rows, but only the newly inserted row. You can achieve that easily with

CREATE TRIGGER sum
BEFORE INSERT
ON news
FOR EACH ROW
SET NEW.sum = (NEW.int_views + NEW.ext_views)/NEW.pageviews

Mind that this is a BEFORE INSERT trigger, as you want to change the row before it is written to the table.

answered Aug 30, 2012 at 19:22

Eugen RieckEugen Rieck

63.6k10 gold badges70 silver badges92 bronze badges

If you try to update/insert on the same table that cause trigger to fire do not use the common sql command like

-> UPDATE TABLE_NAME SET COLUMN_NAME = VALUE WHERE CONDITION_LIST;  
-> INSERT INTO TABLE_NAME VALUES("VALUE1","VALUE2");

This will not work. Only use set to assign the value of the column you update.

Example:

CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE ON table_name
FOR EACH ROW
SET NEW.COLUMN_NAME = "VALUE";

Richard

6,6345 gold badges43 silver badges59 bronze badges

answered Dec 22, 2014 at 8:54

I was in a similar condition where I had to run two triggers:

UPDATE 3 fields on INSERTing a new ROW
UPDATE 3 fields on UPDATEing a ROW

After lot of efforts, I was finally able to write the TRIGGER in following way:

FOR updating values on INSERT

CREATE TRIGGER `INSERT_DISCOUNT_SERVICES` BEFORE INSERT ON `services`
FOR EACH ROW SET 
NEW.discount_5_rate = (NEW.ndis_rate*0.05),
NEW.discount_10_rate=(NEW.ndis_rate*0.10),
NEW.discount_15_rate=(NEW.ndis_rate*0.15)

Similarly

FOR updating values on UPDATE

CREATE TRIGGER `UPDATE_DISCOUNTS_SERVICES` BEFORE UPDATE ON `services`
FOR EACH ROW SET 
NEW.discount_5_rate = (NEW.ndis_rate*0.05), 
NEW.discount_10_rate=(NEW.ndis_rate*0.10),
NEW.discount_15_rate=(NEW.ndis_rate*0.15)

answered Aug 1, 2019 at 11:38

Umar NiaziUmar Niazi

4465 silver badges14 bronze badges

Источник

While working on MySQL triggers this afternoon, I encountered a strange problem that dealt with using a trigger to tweak records in a table after an UPDATE. Specifically, I wanted to write a trigger that would automatically loop over all records in a table, and then mark any «expired» if they were added more than X days ago. I added a trigger using the UPDATE mechanism I thought would work, but nope, I kept getting this error:

ERROR 1442 (HY000): Can't update table 'pass' in stored
   function/trigger because it is already used by statement
   which invoked this stored function/trigger.

Looks intimidating. According to the MySQL forums, a fairly large number of folks have reported this same problem. Sadly, I wasn’t able to find a good solution to the problem, and according to MySQL’s documentation, what I’m trying to do is damn near impossible with triggers.

What I’m Trying To Accomplish

I’ve got a table named «pass» that contains three fields: an ID field, a status, and a TIMESTAMP. The ID field is simply a unique ID number of some sort. The status field is an ENUM, either «active» or «expired». And, the TIMESTAMP indicates when the record was added to the table. The goal here is to use a trigger that automatically sets the status to «expired» on each row that was added more than 7 days ago. I want to let the MySQL trigger engine take care of managing the expired status of these records for me, so that my web-app doesn’t explicitly have to.

The Schema

Here’s the schema for my «pass» table:

CREATE TABLE pass (
  id BIGINT NOT NULL,
  status ENUM('active','expired') NOT NULL DEFAULT 'active',
  addedon TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ( id )
) TYPE=InnoDB;

Note that the default value on the status field is «active» and the default on the TIMESTAMP field is the CURRENT_TIMESTAMP (the current time on the MySQL server when the record was added to the table). Let’s add some records to this «pass» table:

mysql> insert into pass ( id ) values ( '1' );
Query OK, 1 row affected (0.00 sec)

mysql> insert into pass ( id ) values ( '2' );
Query OK, 1 row affected (0.00 sec)

mysql> insert into pass ( id ) values ( '3' );
Query OK, 1 row affected (0.00 sec)

mysql> insert into pass ( id ) values ( '4' );
Query OK, 1 row affected (0.00 sec)

mysql> update pass set addedon = '2009-06-30 22:06:03' where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from pass;
+----+--------+---------------------+
| id | status | addedon             |
+----+--------+---------------------+
|  1 | active | 2009-07-08 22:20:18 |
|  2 | active | 2009-07-08 22:20:19 |
|  3 | active | 2009-06-30 22:06:03 |
|  4 | active | 2009-07-08 22:20:22 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

OK, so I populated the pass table and tweaked the addedon TIMESTAMP of ID 3 so that it’s more than 7 days old. Using a trigger, I want to set the status of this record to «expired» on any UPDATE to the pass table.

The Trigger Problem

The original trigger I wrote used UPDATE pass SET status = 'expired' like so:

DELIMITER |
  CREATE TRIGGER expire_trigger
    BEFORE UPDATE ON pass
    FOR EACH ROW BEGIN
      UPDATE pass SET pass.status = 'expired' WHERE
        (DATEDIFF(NOW(),pass.addedon) > 7);
    END;
|
DELIMITER ;

This looks like it should work, but unfortunately I kept hitting the following error when trying to UPDATE any record in the pass table within the trigger:

ERROR 1442 (HY000): Can't update table 'pass' in stored
   function/trigger because it is already used by statement
   which invoked this stored function/trigger.

In a nutshell the problem here is that I’m trying to use a trigger to UPDATE a table via the operation that invoked the trigger in the first place. According to what I read in the MySQL documentation, this error is generated to prevent infinite recursion: an UPDATE occurs, the trigger is run and updates the table, this trigger UPDATE causes the trigger to run again, and again, and again. Basically without this error, the trigger would trigger itself in an infinite loop.

Trigger Limitations in MySQL

In the end, I concluded that I cannot easily tweak my trigger to accomplish what I’m trying to do. For example, instead of using the UPDATE above, I also tried with procedural like statements:

DELIMITER |
  CREATE TRIGGER expire_trigger
    BEFORE UPDATE ON pass
    FOR EACH ROW BEGIN
      IF (DATEDIFF(NOW(),NEW.addedon) > 7) THEN
        SET NEW.status = 'expired';
      END IF;
    END;
|
DELIMITER ;

This works quite nicely, BUT only on the record that I’m modifying. For example, if I’m updating a record that’s more than 7-days old, then yes, the trigger will change the status to expired for me. However, if I update another record in the table that’s not more than 7-days old, the trigger will not run over ALL records. Basically, it seems that the trigger can only be used to modify/change the record being updated, inserted, or deleted. There does not appear to be a way to write a MySQL trigger that loops over all records when activated.

What About Procedures?

Knowing that triggers won’t let me explicitly loop over and adjust all records in a table on an UPDATE, I gave up and started looking into procedures. A procedure is somewhat similar to a trigger, except that it doesn’t run automatically during an UPDATE, INSERT, DELETE, etc. Instead, the user has to explicitly call a procedure to run it. So, I created a procedure to loop over and expire all records more than 7-days old:

mysql> DELIMITER |
  CREATE PROCEDURE expire_procedure()
    BEGIN
      UPDATE pass SET pass.status = 'expired'
        WHERE (DATEDIFF(NOW(),pass.addedon) > 7);
    END;
|
DELIMITER ;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL expire_procedure();
Query OK, 1 row affected (0.01 sec)

mysql> select * from pass;
+----+---------+---------------------+
| id | status  | addedon             |
+----+---------+---------------------+
|  1 | active  | 2009-07-08 22:20:18 |
|  2 | active  | 2009-07-08 22:20:19 |
|  3 | expired | 2009-06-30 22:06:03 |
|  4 | active  | 2009-07-08 22:20:22 |
+----+---------+---------------------+
4 rows in set (0.00 sec)

Sure enough, the correct record was set to «expired». So, the procedure works fine (I knew it would) but I then tried to call it from a trigger:

mysql> DELIMITER |
  CREATE TRIGGER expire_trigger
    BEFORE UPDATE ON pass
    FOR EACH ROW BEGIN
      CALL expire_procedure();
    END;
|
DELIMITER ;

mysql> update pass set id = 10 where id = 3;
ERROR 1442 (HY000): Can't update table 'pass' in stored
  function/trigger because it is already used by statement
  which invoked this stored function/trigger.

OK, so even using a procedure with a trigger isn’t going to solve my problem. I still see the same error as before.

Conclusion

Bottom line, there doesn’t appear to be a way to use a trigger in MySQL that loops over all rows in a table when activated. And, calling a procedure from a trigger isn’t going to work either. Solutions to this problem might include building your web-app to call your procedure at a given interval. Or, call the procedure when a user logs in (depending on the load). You might even consider using a cron job.

Good luck.

Источник

Содержание

MySQL Error #1442
Name already in use
blog / content / entries / mysql-trigger-error-1442-hy000-cant-update-table-tbl-in-stored-functiontrigger-because-it-is-al.md
Error code 1442 mysql
Вопрос:
Комментарии:
Ответ №1:
Форум пользователей MySQL
#1 26.08.2008 13:54:39
error 1442

MySQL Error #1442

Есть два триггера, которые не могу заставить работать вместе.

При изменении поля в tasks_dicts ловлю ошибку

Гугл говорит, что это происходит от того, что таблица на время выполнения триггера блокируется, но как 1 влияет на 2. Буду рад любым советам.

По-отдельности, первый и второй триггер работают как надо.

З.Ы. Переезжать на другую БД не могу, сроки проекта поджимают.

Да, не даёт. Причём во втором триггере можно писать что угодно.

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

Правда в итоге, людям отдал всю ту беду в PostgreSQL. Там проще оказалось с этими вложенными триггерами. (но еще важнее было, что он входил в состав AstraLinux SE, «проще с триггерами» бонусом оказалось).

Разве после окончания работы триггера он не разблокирует таблицу?

Если память не изменяет — он внутри себя порождает событие, которое приводит к рекурсивному вызову самого себя.

При «обновить А» вызвать «обновить Б», а при «обновлении Б», опять «обновление А» (точнее — удаление, но, видимо, это одно и тоже для MySQL). Т.е. — он не закончился и таблица заблокирована, когда он еще раз пытается себя же стрельнуть и не может.

Поэтому логика такая, что при «обновить А» — надо дергать промежуточное обновление С и заканчиваться, а изменения уже в С отслеживать другим механизмом, в котором обновление Б повторно будет дергать опосредованно обновление А. Вне контекста первого триггера.

Пардон, за идиотское изложение мысли. Надеюсь более/менее понятно, что пытаюсь сказать. У меня такое было на реальной задаче, и другого способа это обойти я не нашел в MySQL.

Собственно, вы правы. В моих двух триггерах не возникает рекурсии, но тем не менее. Обошел через создание дополнительной таблицы и EVENT. Просто каждую минуту дергаю дополнительную таблицу и вызываю процедуру. Ох уж этот убогий MySQL.

Возможно есть более красивые решения. Но я их не нашел. То приложение имело специфику «закрытого инструмента» со спец. доступом и т.д., из которого надо было публиковать в общий доступ малую часть один раз в месяц. Поэтому EVENT можно было хоть раз в сутки вызывать. Для других целей, возможно, это неприемлемо.

MySQL прекрасен для своих задач. В 9 из 10 случаев. Особенно из-за кэширования результатов запросов на ISAM-образных таблицах. Postgres никогда его не догонит по скорости в простых применениях. Но бизнес-логику худо/бедно сложную переносить в MySQL получается не очень.

Источник

Name already in use

blog / content / entries / mysql-trigger-error-1442-hy000-cant-update-table-tbl-in-stored-functiontrigger-because-it-is-al.md

Go to file T
Go to line L
Copy path
Copy permalink

Copy raw contents

While working on MySQL triggers this afternoon, I encountered a strange problem that dealt with using a trigger to tweak records in a table after an UPDATE . Specifically, I wanted to write a trigger that would automatically loop over all records in a table, and then mark any «expired» if they were added more than X days ago. I added a trigger using the UPDATE mechanism I thought would work, but nope, I kept getting this error:

What I’m Trying To Accomplish

I’ve got a table named «pass» that contains three fields: an ID field, a status, and a TIMESTAMP . The ID field is simply a unique ID number of some sort. The status field is an ENUM , either «active» or «expired». And, the TIMESTAMP indicates when the record was added to the table. The goal here is to use a trigger that automatically sets the status to «expired» on each row that was added more than 7 days ago. I want to let the MySQL trigger engine take care of managing the expired status of these records for me, so that my web-app doesn’t explicitly have to.

Here’s the schema for my «pass» table:

Note that the default value on the status field is «active» and the default on the TIMESTAMP field is the CURRENT_TIMESTAMP (the current time on the MySQL server when the record was added to the table). Let’s add some records to this «pass» table:

OK, so I populated the pass table and tweaked the addedon TIMESTAMP of ID 3 so that it’s more than 7 days old. Using a trigger, I want to set the status of this record to «expired» on any UPDATE to the pass table.

The Trigger Problem

The original trigger I wrote used UPDATE pass SET status = ‘expired’ like so:

This looks like it should work, but unfortunately I kept hitting the following error when trying to UPDATE any record in the pass table within the trigger:

In a nutshell the problem here is that I’m trying to use a trigger to UPDATE a table via the operation that invoked the trigger in the first place. According to what I read in the MySQL documentation, this error is generated to prevent infinite recursion: an UPDATE occurs, the trigger is run and updates the table, this trigger UPDATE causes the trigger to run again, and again, and again. Basically without this error, the trigger would trigger itself in an infinite loop.

Trigger Limitations in MySQL

In the end, I concluded that I cannot easily tweak my trigger to accomplish what I’m trying to do. For example, instead of using the UPDATE above, I also tried with procedural like statements:

What About Procedures?

Knowing that triggers won’t let me explicitly loop over and adjust all records in a table on an UPDATE , I gave up and started looking into procedures. A procedure is somewhat similar to a trigger, except that it doesn’t run automatically during an UPDATE , INSERT , DELETE , etc. Instead, the user has to explicitly call a procedure to run it. So, I created a procedure to loop over and expire all records more than 7-days old:

Sure enough, the correct record was set to «expired». So, the procedure works fine (I knew it would) but I then tried to call it from a trigger:

OK, so even using a procedure with a trigger isn’t going to solve my problem. I still see the same error as before.

Источник

Error code 1442 mysql

#mysql #sql #triggers

Вопрос:

Я создал два триггера.

Один из них-это триггер, который вставляет две данные в таблицу платежей, если вы оплачиваете пробег при бронировании. (Оплата пробега, оставшаяся сумма оплачивается другими способами оплаты)

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

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

Код ошибки: 1442. Не удается обновить таблицу «клиент» в сохраненной функции/триггере, потому что она уже используется оператором, который вызвал эту сохраненную функцию/триггер.

Я предполагаю, что эта проблема связана с конфликтом двух вышеперечисленных триггеров. Это связано с тем, что один выбирает данные(пробег) из таблицы клиентов, когда используется весь пробег, а другой обновляет данные(пробег) из таблицы клиентов.

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

Вторая проблема заключается в том , что, когда я устанавливаю @mileuse = «все», я получаю ту же ошибку, что и заголовок.

Как я могу решить эту проблему?

Ответ №1:

Я решил эту проблему немного по-другому.

Я удалил » если @mileuse = все, то …»

И я решил разделить переменную на две части.

Это не кажется фундаментальным решением, но это был лучший способ его реализации.

Источник

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Страниц: 1

#1 26.08.2008 13:54:39

error 1442

ситуация такова:
Имеется 3 таблицы: «test» — таблица тестов, «question» — таблица вопросов, «test_question» — связующая таблица. И триггеры с ними связанные.
Проблема возникает при удалении записи из таблицы test.

CREATE TABLE test (
`id_test` int(10) unsigned NOT NULL auto_increment,
`test_level` enum(‘easy’,’normal’,’hard) NOT NULL,
`short_info` tinytext NOT NULL
PRIMARY KEY (`id_test`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=2 ;

DROP TRIGGER IF EXISTS test_tr
CREATE TRIGGER test_tr BEFORE DELETE ON test
FOR EACH ROW DELETE question FROM question a, test_question b WHERE b.id_test = old.id_test AND a.id_question = b.id_question;

CREATE TABLE question (
`id_question` int(10) unsigned NOT NULL auto_increment,
`text` tinytext NOT NULL,
`point` int(10) NOT NULL
PRIMARY KEY (`id_question`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=2 ;

DROP TRIGGER IF EXISTS quest_tr
CREATE TRIGGER quest_tr BEFORE DELETE ON question
FOR EACH ROW DELETE FROM test_question b WHERE b.id_question = old.id_question;

CREATE TABLE test_question (
`id_test` int(10) unsigned NOT NULL,
`id_question` int(10) unsigned NOT NULL,
UNIQUE KEY `place_sport` (`id_test`,`id_question`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

т.е. вместе с тестом (из табл test) должны удалятся и все вопровы (из табл question), которые в него входят . С другой стороны, при удалении какого-либо вопроса (из табл test) должна удалятся связь теста с удаленным вопросом (из табл test_question). И тот и другой триггер нужны для поддержания целостности БД, но при их взаимном существовании возникает ошибка #1442 — Can’t update table ‘event_sport’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Как я понял проблема в следующем: триггер (quest_tr) пытается обновить таблицу (test_question), которая заблокирована выражением (/триггером test_tr), которое и вызвало этот триггер.
Есть ли пути решения данной проблемы.

Источник

#mysql #sql #triggers

Вопрос:

 delimiter $ 
   create trigger payment_mile_trigger
    after insert on PAYMENT
    for each row
    begin
    if @mileuse = 'all' then
        update customer
        set cust_usedmile = cust_usedmile   cust_currentmile
            and cust_currentmile = 0
        where customer.cust_id = new.cust_id;
    elseif @mileuse > 0 then
        update customer
        set cust_usedmile = cust_currentmile   @mileuse
            and cust_currentmile = cust_currentmile - @mileuse
        where customer.cust_id = new.cust_id;
    end if ;
   end $
delimiter ;

 delimiter $ 
   create trigger Booking_trigger3
    before insert on Booking
    for each row
    begin
      declare pay_totalamount int;
      declare time_stamp timestamp;
      declare pay_type varchar(15);
      declare key_id int;
      declare cust_id int;
      
      set pay_totalamount = new.booking_totalamount;
      set time_stamp = current_timestamp();
      set pay_type = @paytype;
      set key_id = null;
      set cust_id = new.cust_id;
      
    if @mileuse = 'all' then
        insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value ((select cust_currentmile from customer where customer.cust_id=cust_id),
        time_stamp, 'mile',key_id,cust_id);
        insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value (pay_totalamount-(select cust_currentmile from customer where cust_id=cust_id),
        time_stamp, pay_type,key_id,cust_id);
    elseif @mileuse > 0 then
        insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value (@mileuse,
        time_stamp, 'mile',key_id,cust_id);
        insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value (pay_totalamount-@mileuse,
        time_stamp, pay_type,key_id,cust_id);
    else
      insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value (pay_totalamount,time_stamp, pay_type,key_id,cust_id);
    end if;
   end $
delimiter ;

Я создал два триггера.

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

Код ошибки: 1442. Не удается обновить таблицу «клиент» в сохраненной функции/триггере, потому что она уже используется оператором, который вызвал эту сохраненную функцию/триггер.

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

Как я могу решить эту проблему?

Ответ №1:

Я решил эту проблему немного по-другому.

 delimiter $ 
   create trigger Booking_trigger3
    before insert on Booking
    for each row
    begin
      declare pay_totalamount int;
      declare time_stamp timestamp;
      declare pay_type varchar(15);
      declare key_id int;
      declare cust_id int;
      
      set pay_totalamount = new.booking_totalamount;
      set time_stamp = current_timestamp();
      set pay_type = @paytype;
      set key_id = null;
      set cust_id = new.cust_id;

    if @mileuse > 0 then
        insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value (@mileuse,
        time_stamp, 'mile',key_id,cust_id);
        insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value (pay_totalamount-@mileuse,
        time_stamp, pay_type,key_id,cust_id);
    else
      insert into payment(pay_totalamount,pay_date,pay_type,key_id,cust_id) 
        value (pay_totalamount,time_stamp, pay_type,key_id,cust_id);
    end if;
   end $
delimiter ;

Я удалил » если @mileuse = все, то …»

 select @mileuse := '0';
select @mileuse := 
    (select cust_currentmile from customer
        where cust_id = @custid); # all mile pay

И я решил разделить переменную на две части.

Это не кажется фундаментальным решением, но это был лучший способ его реализации.

Источник

What I’m Trying To Accomplish

The Schema

The Trigger Problem

Trigger Limitations in MySQL

What About Procedures?

Conclusion

MySQL Error #1442

Name already in use

blog / content / entries / mysql-trigger-error-1442-hy000-cant-update-table-tbl-in-stored-functiontrigger-because-it-is-al.md

Error code 1442 mysql

Вопрос:

Комментарии:

Ответ №1:

Форум пользователей MySQL

#1 26.08.2008 13:54:39

error 1442

Вопрос:

Комментарии:

Ответ №1: