question

Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 asked EchoLiu-msft edited

Insert data in Parent and child table

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Viorel-1 edited

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

okay, so what are the ways when i m insert data in parent table, it should automatically insert in child table

0 Votes 0 ·

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



0 Votes 0 ·
SurenderSinghBhadauria-3848 avatar image
1 Vote"
SurenderSinghBhadauria-3848 answered SurenderSinghBhadauria-3848 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Mario2286-5314,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.