XML with Merge statement - Problem with deleting dependent tables data deletion
Hi, I'm working with XML formatted input data and using Merge statement. My intention is to completely make use of XML & Merge only. I need to insert/update/delete parent & dependent child tables data. I'm able to handle Insert & Update of child tables data using OUTPUT clause with reference to parent but when i try to delete parent table data, its throwing foreign key constraint violation which is as expected. Can some body help me with any work around to resolve this constraint violation issue and data should be deleted in all dependent tables as well.
To delete data in the dependent tables, regardless of XML or not, you have two choices. You remove the data from those dependent tables first, then delete the data from the parent table, or you enable [cascade delete]. This will automatically remove the child data. Personally, I would never use this because it's a very imprecise mechanism for controlling the data in your system. Plus, depending on the amount of dependent data, this can lead to very large transactions which hurt performance and could lead to severe blocking. You may also see more deadlocks because of the large transactions and because you can't control the order in which data is removed. :