ERROR 1442: Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
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开发者_如何学Python;
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?
- 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;
This is because you are trying to update the other table too. Try that:
UPDATE `trade_request`
SET NEW.title = `trade_request_type`.title ,
NEW.description = `trade_request_type`.description
FROM `trade_request_type`
WHERE `trade_request_type`.id = NEW.trade_request_typeId;
AND `trade_request_type`.id = `trade_request`.id
Three problems:
- To test for null, use
is null
(not= null
which is never true) - To set new values in your incoming row, use
NEW.column = some_value
- You can't update a table twice in one trigger
Try this:
drop trigger trigger_trade_request ;
CREATE TRIGGER trigger_trade_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;
精彩评论