0

Problem: When a row is INSERTed, I must use a trigger to (conditionally) insert another row in the same table. I must put "NEW.rowID" in a different field of the "another" row (rowID being the primary key).

Using the same table as the one that caused the trigger is not possible within a trigger, and this is the reason for getting error #1442. This is understood.

This accepted answer suggests a workaround, but it seems this would not be a trigger anymore, correct? Instead, I would call (with program code) the Stored Procedure with all the field values and it would perform the INSERT from that. And if I put the SP call inside a trigger to let us insert by using an INSERT statement, I get error #1422 again. Right?

If I understood the above correctly, is there an alternative solution to keep it a trigger? (Boss would like a trigger to save on program code changes.)

What I've tried: Replacing the trigger code with an INSERT with hard-coded values, looked for answers, read documentation on transactions.

Henrik Erlandsson
  • 3,797
  • 5
  • 43
  • 63
  • If MySQL allow you to do that, don't you think your method would keep adding new rows endlessly? – Krish May 16 '19 at 14:42
  • Why must you use a trigger? – P.Salmon May 16 '19 at 14:50
  • @krishKM the insert is conditional, updating question. Many want this function, as you can see on SO. (E.g. when billing work, automatically bill this fee also.) Triggers simplify the application code. – Henrik Erlandsson May 17 '19 at 07:34

0 Answers0