Insert data in Parent and child table

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.