Error 1442 / trigger failing / due to select in insert statement
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
Subject
Views
Written By
Posted
Error 1442 / trigger failing / due to select in insert statement
801
April 27, 2022 03:45PM
357
April 28, 2022 09:34AM
Sorry, you can't reply to this topic. It has been closed.
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.