469,110 Members | 1,986 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

updating and deleting parent and child table simultaneously

I have two table both say A and B.
If i insert a record in A that record should be inserted in B.
If i delete a record in A that record should be deleted from B.

Is that possible.If yes please tell me.

Thankyou in advance,
vishnu
Jul 20 '05 #1
3 3241
Hi

Without table structures (DDL - create table statements etc.. ) it is hard
to know exactly what you require. If the tables are different then usually
the records are inserted separately within a transaction, often using a
stored procedure. The deletions are usually taken care of by defining a
foreign key relationship between the child (referencing) table and the
parent (referenced) table. More information about these can be found in
books online.

John


"vishnu mahendra" <vi*************@yahoo.com> wrote in message
news:c2*************************@posting.google.co m...
I have two table both say A and B.
If i insert a record in A that record should be inserted in B.
If i delete a record in A that record should be deleted from B.

Is that possible.If yes please tell me.

Thankyou in advance,
vishnu

Jul 20 '05 #2
vishnu mahendra (vi*************@yahoo.com) writes:
I have two table both say A and B.
If i insert a record in A that record should be inserted in B.
If i delete a record in A that record should be deleted from B.

Is that possible.If yes please tell me.


Yes, this is possible. For the insertion part, you would need to
have a trigger:

CREATE TRIGGER replicate_tri ON A FOR INSERT AS
INSERT B (...)
SELECT ...
FROM inserted
go

For the deletion part, you could have another trigger, but you could
also set up a cascading foreign-key constraint as John suggested.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
>> If I insert a record [sic] in A that record [sic] should be inserted
in B. If I delete a record [sic] in A that record [sic] should be
deleted from B. <<

If you have two identical tables in the same schema, you have a
redundancy and need to get rid of one of them.

If this was a file system -- where they DO have records and not rows
(HUGE difference)!! -- then you might expect this sort of design.

Back in the 1950's we would often take one tape and make mulitple copies
of the data, sometimes in a different order, so we could run several
reports at the same time. Is this what you are doing?

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.