Insert data in Parent and child table

Question

Insert data in Parent and child table

Mario2286 441

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

Accepted answer

3 additional answers

Your answer

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

Surender Singh Bhadauria 6

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

Olaf Helper 47,441

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.

Mario2286 441 Reputation points

2021-04-23T09:09:43.42+00:00

okay, so what are the ways when i m insert data in parent table, it should automatically insert in child table
Viorel 122.6K Reputation points

2021-04-23T16:37:59.703+00:00

For example, create a trigger (see CREATE TRIGGER … ON MyParentTable AFTER INSERT …), which will automatically insert some random data to child tables.

Share via

Insert data in Parent and child table

3 additional answers

Your answer