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.
asked Apr 21, 2014 at 11:32 AM in Default
You can try triggers.
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!
answered Apr 21, 2014 at 01:26 PM
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.
answered Apr 22, 2014 at 04:07 AM