September 28, 2004 at 1:30 am
Hi
Our application can support both Oracle and SQL Server
as back-end. We use Cascade Deletes to delete rows from
the Child table if the parent table record is deleted. This was our
approach with Oracle. We are told that SQL Server
has issues while using Cascade deletes and we need to take
a different approach (rather a AFTER DELETE trigger on Parent Table).
Is anyone aware of any such issues on SQL Server? If yes, then
is there any other solution other than the AFTER DELETE trigger?
Thanks in advance.
Regards
Ramkumar
September 28, 2004 at 2:27 am
In SQL2000 when declaring referential integrity there is an option to "Cascade Delete" related records.
If this option is set then deleting a parent will cascade delete the children with no problems. You don't need a trigger to do this.
However, I believe that this feature was added in SQL2000 so if you are planning to support SQL7 and below you will need to use some other method. Also, you don't have INSTEAD OF triggers in SQL7.
September 29, 2004 at 12:02 am
Hi
Thanks for the reply. We are aware that Cascade delete option
is available while enforcing referential integrity in SQL Server 2000.
We are told that SQL Server 2000 has issues with Cascade Delete.
What we require is to know what exactly is the issue and possibly
a solution for that.
Regards
Ramkumar
September 29, 2004 at 2:17 am
I am not aware of any issues with it other than the obvious mechanics i.e. you accidentally delete the wrong parent record and lose reams of irreplaceable data.
I know that you can have up to 32 cascading triggers but I don't know whether this applies to cascade deletes.
BOL mentions a few points to watch out for.
Apart from that I would think the other obvious points would be to watch out for locks or performance bottle necks. Good old fashioned common sense.
I think the best thing would be to go back to your source and get the context of what these "issues" are.
September 29, 2004 at 8:31 am
I ran into this problem awhile back with a fairly complex database. SQL Server's cascade delete could not handle self-referencing columns, such as a ManagerId Column in an Employee table that references back to an EmployeeId in that same table. Our database used quite a few of these parent-child references and the Cascade Delete did not work.
If I remember right, it was also a problem when a key in one table served as a foreign key in more than one other table.
I tried a number of approaches, such as using a mix of Cascade Deletes and Delete Triggers, and also tried using a recursive delete stored procedure. This database had a lot of levels of relationships: Table 1 is related to Table 2 is related to Table 3, etc., and getting the order of the deletes correct was a problem. In the end, I went through each table and created an Instead Of Delete trigger, which included a delete for each child table directly related to it, then finally deleting itself, such as:
CREATE TRIGGER TrgOrganizationD ON [dbo].[Organization]
INSTEAD OF DELETE
AS
DELETE FROM OrgHierarchy
WHERE ChildOrganizationId IN
(SELECT OrganizationId FROM DELETED)
DELETE FROM OrgHierarchy
WHERE OrganizationId IN (SELECT OrganizationId FROM DELETED)
DELETE FROM Organization
WHERE OrganizationId IN (SELECT OrganizationId FROM DELETED)
It was painful, but at least this way I know that the order is correct even with several depth levels, because when the child record is deleted it goes through its own INSTEAD OF DELETE trigger.
September 29, 2004 at 8:37 am
This confirms what is written in BOL i.e. each table can only appear once in a cascade relationship and you can't have circular references.
Basically, you have to design your database schema with cascade deletes in mind.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply