x

batch which will modify the child table if any record is updated in its parent table.

i need to create a batch which will modify the child table if any record is updated in its parent table.suppose i have a parent table A and it has 4 child table A1,A2,A3,A4 ,now if any record in table A is modified or any new record is inserted in table A it should be reflected in all its child tables.
more ▼

asked Apr 21 at 11:32 AM in Default

Chitra_Maithani gravatar image

Chitra_Maithani
11 1 1

This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
Apr 27 at 01:19 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
more ▼

answered Apr 21 at 12:48 PM

vivekyadav0212 gravatar image

vivekyadav0212
481 1 8

(comments are locked)
10|1200 characters needed characters left

Is the solution for an Oracle instance or SQL instance? You've tagged the question as both but I wasn't sure.

If for a SQL Instance, as mention by @vivekyadav0212 you could try triggers.

You could also try cascading updates (older reference--> http://technet.microsoft.com/en-us/library/aa933119(v=sql.80).aspx) as a method to update the children tables. However, this wouldn't work with new records being inserted. Cascading updates/deletes will only function on existing rows.

I don't know how you are the updates are being introduced (ie: web app, windows app, manually, entity framework, etc) but you could also look at implementing the changes through stored procedures. If you know that you'll always have to update certain child tables, you could account for those tables in a stored procedure.

I usually tend to lean towards the latter (given that it fits the need) as it allows me to better control various performance issues if needed as well as being a more flexible to handle stuff as things change.

If it's for an Oracle instance, I'd have to refer to my Google-Fu to find a solution. ;-)

Hope this helps!
more ▼

answered Apr 21 at 01:26 PM

JohnM gravatar image

JohnM
6.6k 1 3 7

(comments are locked)
10|1200 characters needed characters left

If the updates to child tables not to be in synchronous fashion, you could use Change Data Capture feature to audit changes and have a batch job to carry forward changes to child tables. The below is a good introduction on Change Data capture feature of SQL Server. https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

As @JohnM suggested, it is better to handle the child table changes, as part of the stored procedure making changes to parent table. You will have more control over the changes.

Using triggers, make the transaction longer and locks in the tables longer.
more ▼

answered Apr 22 at 04:07 AM

Venkataraman gravatar image

Venkataraman
1k 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x713
x378

asked: Apr 21 at 11:32 AM

Seen: 351 times

Last Updated: Apr 27 at 01:19 PM