How to insert a row into table automatically?

I have two tables (parent & child) in my database. How can I insert a new record into the parent table so that a record is automatically inserted into c开发者_运维知识库hild table?

You can use (after insert) triggers for that, see MySQL manual for syntax.

Here's an example of using a trigger to insert a new child record with the just-generated parent auto-increment id.

mysql> create table ParentTable (id int auto_increment primary key);

mysql> create table ChildTable (id int auto_increment primary key,
       parentId int, foreign key (parentId) references ParentTable(id));

mysql> CREATE TRIGGER MyTrigger AFTER INSERT ON ParentTable
    -> FOR EACH ROW
    -> INSERT INTO ChildTable (parentId) VALUES (NEW.id);

mysql> insert into ParentTable () values ();
Query OK, 1 row affected (0.02 sec)

mysql> select * from ChildTable;
+----+----------+
| id | parentId |
+----+----------+
|  1 |        1 |
+----+----------+

This is a trivial example, because the two tables don't have any other columns except for their primary keys and the foreign key.

But what if you want to insert other columns into the child table?

The trigger only has access to the NEW.* columns of the row it just inserted into the parent. It may also SELECT other existing data from elsewhere in the database. Or it could use hard-coded literal values.

There may also be defaults on the other columns in the child table, or else it may be okay for them to be NULL temporarily, until you can fill them in with subsequent UPDATE statements.

You have to accept that the trigger might not be able to give specific values for the relevant columns as it INSERTs to the child table.