question

Chitra_Maithani avatar image
Chitra_Maithani asked

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.
sqloracle
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
vivekyadav0212 avatar image
vivekyadav0212 answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Venkataraman avatar image
Venkataraman answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.