Insert data in Parent and child table

asked 2021-04-23T08:52:31.597+00:00
Mario2286 421 Reputation points

I know update and delete cascade will update and delete automatically in child table when any changes happen in parent table but how about insert data in parent , does insert data in parent table will automatically insert in child table

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,467 questions
No comments
{count} votes

Accepted answer
  1. answered 2021-04-23T21:26:02.24+00:00
    Erland Sommarskog 67,481 Reputation points Microsoft MVP

    Here is a script to illustrate what Viorel said. I can't say that I find the request particular meaningful - in most cases you don't know the child data only from the parent.

    CREATE TABLE parent (ParentId int NOT NULL,
                         ParentName varchar(20) NOT NULL,
                         CONSTRAINT pk_parent PRIMARY KEY (ParentId)
    )
    go
    CREATE TABLE child (ParentId int NOT NULL,
                        ChildNo  int NOT NULL,
                        ChildName varchar(20) NOT NULL,
                        CONSTRAINT pk_child PRIMARY KEY (ParentId, ChildNo),
                        CONSTRAINT fk_child_parent FOREIGN KEY (ParentId) REFERENCES parent (ParentId)
    )
    go
    CREATE TRIGGER parent_tri ON parent AFTER INSERT AS
      INSERT child (ParentId, ChildNo, ChildName)
         SELECT ParentId, 1, ParentName
         FROM   inserted
    go
    INSERT parent(ParentId, ParentName)
       VALUES(11, 'Jim')
    go
    SELECT * FROM parent
    SELECT * FROM child
    go
    DROP TABLE child, parent
    
    No comments

3 additional answers

Sort by: Most helpful
  1. answered 2021-04-24T15:06:54.197+00:00
    Surender Singh Bhadauria 6 Reputation points

    Have look at the below Post. Even though the example used is quite simple.It can be used with the "Instead of Insert" Trigger.

    how-to-insert-data-using-sql-views.html

    create-trigger-transact-sql

    No comments

  2. answered 2021-04-26T08:20:28.12+00:00
    EchoLiu-MSFT 14,416 Reputation points

    Hi @Mario2286 ,

    I want to know how your parent table and child table are implemente: update and delete cascade will update and delete automatically in child table when any changes happen in parent table.

    As far as I know, the role of triggers is that when operations such as update, insert, and delete are performed on the table, the system will automatically call and execute the corresponding triggers on the table.You can try to use triggers.

    If the trigger does not solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) So that we’ll get a right direction and make some test.

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.

    No comments

  3. answered 2021-04-23T09:00:15.123+00:00
    Olaf Helper 25,476 Reputation points

    does insert data in parent table will automatically insert in child table

    How could the database engine know what data to insert beside the key? No, it can and so it don't.