MySQL Forums
Forum List  »  Triggers

Error 1442 / trigger failing / due to select in insert statement
Posted by: Stewart Bourke
Date: April 27, 2022 03:45PM

First, apologies for cross-posting - Posted to the wrong forum initially:

Good evening.

I am aware of the cause of error 1442 in relation to not being able to update a row in a table being called from the trigger in general, but I cannot understand why a trigger fails if the insert statement contains only a select from the table in question

Create trigger t1 in rating
for each row
begin
update title set f1 = new.vote where title.id = new.title_id
end

My insert is:

insert into rating (title_id, vote) values (select id from title where title_name = 'FRED1',25);

The issue is because I am selecting a value from the title table, I cannot update it in the body of the trigger. Surely if it is only selecting then it does not have the potential to cause an infinite-loop, which is what I understand to be the reason why this is not allowed?

Having searched around, I cannot find how to get around this? Are there any 'standard' techniques that people are aware of?

Thank you

Stewart Bourke

Options: ReplyQuote


Subject
Views
Written By
Posted
Error 1442 / trigger failing / due to select in insert statement
260
April 27, 2022 03:45PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.