0

I have 2 tables new1(id int, cname varchar(255)) and new2(id int). I am trying to execute the following statements but i am getting an error.

delimiter |
create trigger tr_new1 
after insert on new1
for each row
begin
    insert into new2 values(new.id);
end;
|
delimiter ;

insert into new1 values(10, 'dhruv');

ERROR : Error Code: 1442. Can't update table 'new1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

I know about the infinite loop problem that one might get into if we use trigger on same table. However, here i have different tables, why am i getting this error ?

Dhruv Chadha
  • 1,161
  • 2
  • 11
  • 33
  • A better question is, "would you ever want to update a table in a trigger"? The answer is an emphatic "**NO!**" because the developers that follow you would not expect it. – Alvin Thompson Nov 08 '16 at 15:08
  • Do you have other triggers running on those tables? – juergen d Nov 08 '16 at 15:13
  • Do you have an on insert trigger on new2, that inserts a record into new1? – Shadow Nov 08 '16 at 15:14
  • @AlvinThompson then don't hire them or anyone if you can't explain the system to them. – Drew Nov 08 '16 at 15:19
  • As for the close, you and only you can trace back what brought into being the Trigger firing. If that occurred in another Trigger, then go seek it out. – Drew Nov 08 '16 at 15:24
  • @Drew: the point is, projects already naturally tend to get so complex that they tax even a good developer's ability to keep track of everything. Every step of the way you have to fight against adding additional stuff you have to keep track of. What he's trying to accomplish should not be accomplished in this way because it adds to the to the stuff you have to keep track of. I'm really not saying this to be combative, but your attitude simply convinces me that you have little if any experience managing large projects. – Alvin Thompson Nov 08 '16 at 15:30
  • Wow, I have worked for some pretty large ones, am always the tech lead, and have bought several homes from it. But if you say so. – Drew Nov 08 '16 at 15:33
  • @Drew: Ok, if **you** say so... – Alvin Thompson Nov 08 '16 at 15:34
  • Ok, you know more about mysql than me and everything in life. – Drew Nov 08 '16 at 15:34

0 Answers0