There are two tables with the same ID field (in my opinion this is called a one-to-one relationship). These fields are linked through links. Data is added to one table. It is necessary for this ID to immediately appear in another. How can this be done, other than the obvious, immediately add to two tables? Preferably by means of mySQL

  • And you can still clearly show the structure of tables 1 and 2. Show what belongs to what .... and show clearly what you are inserting and what result you expect? - Alexey Shimansky
  • one
    Connections one to one, and even with the auto show ... I wonder why this suddenly took and not to change anything in the architecture. For example, to collect everything in one table ... - Mike
  • This is another question really. I, too, came to mind. Just have a view count. It is updated regularly. And there is a field with the last update time. And it is undesirable that it takes into account the update of the counter (and this is happening). Therefore, I decided to move it to another table. If you can advise in the comments how to get around this (and this is not contrary to the site’s policy), then you would be happy - Zhenya Vedenin
  • one
    I understand that the counter is updated by a separate request, other data does not change ... and the update date is probably a timestamp type field. Then update the counter as something like this. update table set counter=counter+x, tstamp=tstamp where ... Those. you explicitly assign the current value to the timestamp field, then it will not be updated :) - Mike
  • Thank you, I will think about how best)))) Maybe it’s really one thing to do - Zhenya Vedenin

1 answer 1

Create a TRIGGER that will automatically add an entry to another table.

 CREATE TRIGGER insert_table AFTER INSERT ON table_name FOR EACH ROW INSERT INTO table_name_two VALUES (NEW.id)

In general, it is correct to use several INSERT requests within a single transaction, which would be convenient and obvious for other developers who will support your code. Because there is such a share of developers who shove everything that is possible in stored procedures and triggers, and then you won’t understand why you only execute this request, and then another 100,500 locks happened somewhere and the records changed.

  • Thank you, read. But do you think that it is better not to complicate and make a double INSERT? - Zhenya Vedenin
  • Yes, there is nothing terrible in the fact that you are performing two INSERTs within one transaction, if your business logic requires it. - Firepro
  • Already read the amended answer) Thank you. But thanks for the triggers too - Zhenya Vedenin