February 6, 2010 at 7:40 am
Hi,
How do I delete cascade child tables and logging the number of deleted records per child tables?
I have here a create script having the following:
TableStatistics = where we log the number of deleted records together with the table name.
Table A = The parent table
Tables B,C, and D = Child tables
CREATE TABLE TableStatistics (
ID int IDENTITY NOT NULL PRIMARY KEY,
TableName varchar(10),
RowsDeleted int
)
CREATE TABLE A (
A_ID int IDENTITY NOT NULL PRIMARY KEY,
[Desc] nvarchar(max)
)
CREATE TABLE B (
B_ID int IDENTITY NOT NULL PRIMARY KEY,
A_ID int,
[Desc] nvarchar(max),
CONSTRAINT foreign_b_id FOREIGN KEY (A_ID) REFERENCES A(A_ID)
)
CREATE TABLE C (
C_ID int IDENTITY NOT NULL PRIMARY KEY,
A_ID int,
[Desc] nvarchar(max),
CONSTRAINT foreign_c_id FOREIGN KEY (A_ID) REFERENCES A(A_ID)
)
CREATE TABLE D (
D_ID int IDENTITY NOT NULL PRIMARY KEY,
A_ID int,
[Desc] nvarchar(max),
CONSTRAINT foreign_d_id FOREIGN KEY (A_ID) REFERENCES A(A_ID)
)
GO
Here are the data that I populated in order to test the integrity and performance of the script.
INSERT A ([Desc])
SELECT 'Desc1' UNION ALL
SELECT 'Desc2' UNION ALL
SELECT 'Desc3' UNION ALL
SELECT 'Desc4' UNION ALL
SELECT 'Desc5' UNION ALL
SELECT 'Desc6'
GO 10000
INSERT B ([Desc])
SELECT 'Desc7' UNION ALL
SELECT 'Desc8' UNION ALL
SELECT 'Desc9' UNION ALL
SELECT 'Desc10' UNION ALL
SELECT 'Desc11' UNION ALL
SELECT 'Desc12' UNION ALL
SELECT 'Desc13'
GO 10000
INSERT C ([Desc])
SELECT 'Desc13' UNION ALL
SELECT 'Desc14' UNION ALL
SELECT 'Desc15' UNION ALL
SELECT 'Desc16' UNION ALL
SELECT 'Desc17' UNION ALL
SELECT 'Desc18' UNION ALL
SELECT 'Desc19' UNION ALL
SELECT 'Desc20'
GO 10000
INSERT D ([Desc])
SELECT 'Desc19' UNION ALL
SELECT 'Desc20' UNION ALL
SELECT 'Desc22' UNION ALL
SELECT 'Desc23' UNION ALL
SELECT 'Desc24' UNION ALL
SELECT 'Desc25' UNION ALL
SELECT 'Desc26' UNION ALL
SELECT 'Desc27' UNION ALL
SELECT 'Desc28'
GO 10000
I was about to use the ON DELETE CASCADE functionality but there were circumstances that I cannot use it:
1.) Since I need to log statistics after every delete, I don't know if it is efficient to call a Trigger after DELETE
2.) We are processing Millions of data, firing Trigger could go nuts correct me if I am wrong.
Please tell me what is the best solution to this. Any sample script is highly appreciated.
Thanks,
James
February 13, 2010 at 8:31 am
I actually think the trigger is the way to go for a couple of reasons.
First, I don't see how you can do the logging without it. That's the big one. Cascading deletes are just to maintain integrity and not for logging purposes.
Second, you can get better control over your deletes with a trigger. Cascading deletes can be very slow because, if I'm not mistaken, the cascade is fired on a per row basis per table meaning a lot of individual row deletes through the cascade. With an INSTEAD OF trigger, you can join the DELETED table to your child tables to do the delete and then use the @@ROWCOUNT variable to log the affected record count and then continue to the next table, and then when finished with the children, finally do the delete from the primary table.
I do not think the trigger will be a performance hit in this case. In fact, I think it will be a performance gain.
Tim Januario
February 15, 2010 at 12:58 am
Try some thing like this....
DECLARE @Cnt int
DECLARE @CASCADELevel TABLE
(
AID INT,
ADESC nvarchar(max),
BID INT,
BDESC nvarchar(max),
CID INT,
CDESC nvarchar(max),
DID INT,
DDESC nvarchar(max)
)
;WITH CTEDELETE (AID,ADESC,BID,BDESC,CID,CDESC,DID,DDESC,Level)
AS
(
SELECT
A.A_ID AS AID,
A.[DESC] AS ADESC,
B.B_ID AS BID,
B.[DESC] AS BDESC,
C.C_ID AS CID,
C.[DESC] AS CDESC,
D.D_ID AS DID,
D.[DESC] AS DDESC,
0 AS Level
FROM A AS A
INNER JOIN B AS B
ON (A.A_ID = B.B_ID)
INNER JOIN C AS C
ON (B.B_ID = C.C_ID)
INNER JOIN D AS D
ON (C.C_ID = D.D_ID)
WHERE A.A_ID = 1
UNION ALL
SELECT
CTED.AID,
CTED.ADESC,
CTED.BID,
CTED.BDESC,
CTED.CID,
CTED.CDESC,
CTED.DID,
CTED.DDESC,
Level
FROM D AS A
INNER JOIN CTEDELETE AS CTED
ON (A.A_ID = CTED.AID)
)
INSERT INTO @CASCADELevel
SELECT AID,ADESC,BID,BDESC,CID,CDESC,DID,DDESC FROM CTEDELETE ORDER BY Level
DELETE D
FROM D AS D
INNER JOIN @CASCADELevel AS CL
ON (D.D_ID = CL.DID)
SET @Cnt = @@ROWCOUNT
INSERT INTO TableStatistics VALUES ('D',@Cnt)
SELECT * FROM TableStatistics
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply