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
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
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
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.
okay, so what are the ways when i m insert data in parent table, it should automatically insert in child table
For example, create a trigger (see CREATE TRIGGER … ON MyParentTable AFTER INSERT …), which will automatically insert some random data to child tables.
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
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.
15 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index