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
2
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
64.2k10 gold badges70 silver badges92 bronze badges
1
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,8325 gold badges45 silver badges60 bronze badges
answered Dec 22, 2014 at 8:54
1
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
4765 silver badges14 bronze badges
CREATE DEFINER=root@localhost
TRIGGER bi_trigger_in_employees
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.entpr_name = COALESCE( NEW.entpr_name,
( SELECT ap_name
FROM companies
WHERE id = NEW.entpr_id
LIMIT 1
)
),
NEW.employee_name = COALESCE( NEW.employee_name,
( SELECT CONCAT_WS(' ',np_surname,np_name,np_patronymic)
FROM people
WHERE id = NEW.employee_id
LIMIT 1
)
);
1) Суть ошибки — именно та, что написана в сообщении. Нельзя UPDATE таблицы, на которой определён триггер (и другие DML — тоже нельзя).
2) Изменять вставляемые значения надо до вставки, а не после. Потому BEFORE триггер.
3) По-хорошему, нужен ещё аналогичный BEFORE UPDATE триггер.
4) Все SET выполняются в одном операторе (см. https://dev.mysql.com/doc/refman/8.0/en/set-variab…). А триггер из одного оператора обходится и без BEGIN-END, и без DELIMITER.
5) При скалярном присвоении из подзапроса ВСЕГДА добавляйте LIMIT 1. А по-хорошему ещё и ORDER BY. На показанный код ни то, ни другое не влияет — всё равно одна запись, ибо отбор по PK. Но вдруг структура поменяется, а про триггер забудете? Начнутся ошибки — оно надо?
тогда да, ошибка на бесконечный цикл. Но тут почему?
Как это ни странно, но в теле триггера неизвестно, на какое событие определён триггер.
i wish to do the following,
drop database ABC;
create database ABC;
use ABC;
create table head (
head_id int primary key auto_increment,
head_name varchar(55)
);
create table table1 (
t1_id int primary key,
t1_name varchar(55),
foreign key (t1_id) references head(head_id)
);
insert into head(head_name) values ('table1');
insert into table1(t1_id,t1_name) select max(head_id),'HI' from head;
select * from head;
select * from table1;
but i wish to use the triggers as i have to this for a long heiriechy, so i wrote following code but it’s not working because of mentioned error.
drop database ABC;
create database ABC;
use ABC;
create table head (
head_id int primary key auto_increment,
head_name varchar(55)
);
create table table1 (
t1_id int primary key,
t1_name varchar(55),
foreign key (t1_id) references head(head_id)
);
delimiter $$
CREATE
TRIGGER `t1` BEFORE INSERT
ON `table1`
FOR EACH ROW BEGIN
insert into head (head_name) values ('table1');
END
$$
delimiter ;
insert into table1(t1_id,t1_name) select max(head_id),'HI' from head;
select * from head;
select * from table1;
Above trigger is giving me error,
Error Code: 1442. Can't update table 'head' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Any help!
Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
Issue
I have a table that I want : when the table has been updated, 2 fields of that (title and description) change and take value from another table
This is my trigger:
drop trigger trigger_trade_request ;
CREATE TRIGGER trigger_trade_request AFTER UPDATE ON `trade_request`
FOR EACH ROW
BEGIN
IF NEW.title = null THEN
UPDATE `trade_request_type`,`trade_request`
SET NEW.title = `trade_request_type`.title ,
NEW.description = `trade_request_type`.description
WHERE `trade_request_type`.id = NEW.trade_request_typeId;
END IF;
END;
My tables:
- trade_request_type
- trade_request
error:
ERROR 1442 : Can’t update table ‘trade_request’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
It does not work! What is the prob?
Solution
- Use
is null
to check for null values. - To change the value of the updated row use
BEFORE
trigger andSET
on theNEW
row.
Try this:
CREATE TRIGGER trigger_update_request BEFORE UPDATE ON `trade_request`
FOR EACH ROW
BEGIN
IF NEW.title IS NULL THEN
SET NEW.title = (SELECT title FROM trade_request_type
WHERE trade_request_type.id = NEW.trade_request_typeId);
SET NEW.description = (SELECT description FROM trade_request_type
WHERE trade_request_type.id = NEW.trade_request_typeId);
END IF;
END;
Probably you can use the SELECT...INTO
syntax instead of SET
, like so:
CREATE TRIGGER trigger_update_request BEFORE UPDATE ON `trade_request`
FOR EACH ROW
BEGIN
IF NEW.title IS NULL THEN
SELECT title, description
FROM trade_request_type
WHERE trade_request_type.id = NEW.trade_request_typeId
INTO NEW.tile, NEW.description;
END IF;
END;
Answered By – Xint0
This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0