September 20, 2011 at 12:08 pm
If I wanted to wrap some DELETE DMLs within TRY-CATCH blocks and the multiple DELETE comands are for child tables of parent child relations would you recomend wrapping each DELETE within its own TRY-CATCH block and as each Block executes, move to the next only if the prior suceeded or wrap all the DELETE commands in a single TRY-CATCH block?
My initial idea was to do something like the below and then thought it might be better to wrap each in its own TRY-CATCH block. I do know that if one delete fails/errors then none of the others should commit. Based on that does it matter if all are in one TRY-CATCH or each in its own?
This is being considered to deal with bulf deleteion issue where the system we use requires deleeting one Parent (and its related children data) one at a time and when you have hundreads to do its a lot of wasted man hours (on a regular basis) to do it manually via the system.
Thoughts?
DROP PROCEDURE dbo.DeleteSomething
GO
CREATE PROCEDURE dbo.DeleteSomething(@hID NUMERIC(18,0))
AS
BEGIN
DECLARE @hParentID NUMERIC(18,0)
SET @hParentID = @hID
BEGIN TRANSACTION
BEGIN TRY
DELETE FROM CHILD_TABLEA
WHERE hParentID = @hParentID
DELETE FROM CHILD_TABLEB
WHERE hParentID = @hParentID
DELETE FROM PARENT_TABLE
WHERE hID = @hParentID
END TRY
BEGIN CATCH
/*Call Record Error To ErrLog*/
INSERT INTO dbo.ERRLOGTABLE(ErrNum, ErrSev, ErrState, ErrProc, ErrLine, ErrMsg)
SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE()
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
Kindest Regards,
Just say No to Facebook!September 20, 2011 at 2:03 pm
Why don't you use referential integrity (foreign key) with cascade delete?
September 20, 2011 at 2:36 pm
LutzM (9/20/2011)
Why don't you use referential integrity (foreign key) with cascade delete?
This is a databvase created/used by an accounting application we use. We didn't create it and so we can't change it, only work around it in as passive a manner as possible. I can create custom views of existing objects but actively altering the schema, changing relationships and so on is a support breaker and could possibly get me fired.
Any input on CTE's and multiple DELTE DML's inside of one?
Kindest Regards,
Just say No to Facebook!September 22, 2011 at 12:15 am
i m also looking for solution to this problem.. any help would be highly appreciated!!
September 22, 2011 at 1:39 pm
Given the inability to do this via RI it looks like what you posted originally should work fine. The painful limitation of course is that it can only handle it one row at a time. Can you put triggers on the existing tables without breaking your SLA? If so that would be the next approach. Otherwise go with what you posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2011 at 12:20 pm
To further what Sean pointed out you could do it with INSTEAD OF triggers. If it won't break your SLA with the vendor.
Here's an example:
CREATE TABLE T1
( T1ID INT NOT NULL PRIMARY KEY CLUSTERED
, SomeTextVARCHAR(50)
)
GO
CREATE TABLE T2
( T2ID INT IDENTITY (1, 1)
, T1ID INT NOT NULL
, SomeText VARCHAR(50))
GO
ALTER TABLE T2 ADD CONSTRAINT FK_T2_T1 FOREIGN KEY (T1ID)
REFERENCES T1 (T1ID)
GO
INSERT INTO T1
( T1ID, SomeTxt )
SELECT 1, 'One' UNION ALL
SELECT 2, 'Two'
INSERT INTO T2
( T1ID, SomeText )
SELECT 1, 'Child One' UNION ALL
SELECT 2, 'Child Tow'
GO
DELETE FROM T1
WHERE T1ID = 1
GO
-- Produces an error
-- Create INSTEAD OF trigger to delete from child to parent.
CREATE TRIGGER T1_Del ON T1 INSTEAD OF DELETE
AS
SET NOCOUNT ON;
DELETE T2
FROM deleted d
INNER JOIN T2 T2 ON
D.T1id = T2.T1ID
DELETE T1
FROM deleted D
INNER JOIN T1 T1 ON
D.T1ID = T1.T1ID
GO
DELETE FROM T1
WHERE T1ID = 1
SELECT * FROM T1
September 30, 2011 at 12:46 pm
Sean Lange (9/22/2011)
Given the inability to do this via RI it looks like what you posted originally should work fine. The painful limitation of course is that it can only handle it one row at a time. Can you put triggers on the existing tables without breaking your SLA? If so that would be the next approach. Otherwise go with what you posted.
Well the trigger is one of those iffy things. Meaning Iffy I a put it on a table and the vendor not findeth out then all is well. if however they do they most likely will say we can't support your problem till that is put back like it should be and so I end up dropping the trigger.
We have the DB in our system and so the vendors support people remote into it and cannot access the DB directly thru SSMS or another tool without my explicitly granting them the access (and I remove that access after a support issue is resolved). That said it’s a lot of work to enable/disable something like this whenever we need support so I try to avoid these especially if they are looking at being used on a regular schedule as opposed to a temporary basis.
That said I think I may have not worded my initial question properly since most of the replies are offering alternatives and not input about which of the 2 alternatives I proposed would be better. One poster even mentioned they are having the same problem and mine isn’t a problem so much as which do you think is better between solution A & solution B.
Thanks to all for replying
Kindest Regards,
Just say No to Facebook!September 30, 2011 at 12:56 pm
YSLGuru (9/30/2011)
Sean Lange (9/22/2011)
Given the inability to do this via RI it looks like what you posted originally should work fine. The painful limitation of course is that it can only handle it one row at a time. Can you put triggers on the existing tables without breaking your SLA? If so that would be the next approach. Otherwise go with what you posted.Well the trigger is one of those iffy things. Meaning Iffy I a put it on a table and the vendor not findeth out then all is well. if however they do they most likely will say we can't support your problem till that is put back like it should be and so I end up dropping the trigger.
We have the DB in our system and so the vendors support people remote into it and cannot access the DB directly thru SSMS or another tool without my explicitly granting them the access (and I remove that access after a support issue is resolved). That said it’s a lot of work to enable/disable something like this whenever we need support so I try to avoid these especially if they are looking at being used on a regular schedule as opposed to a temporary basis.
That said I think I may have not worded my initial question properly since most of the replies are offering alternatives and not input about which of the 2 alternatives I proposed would be better. One poster even mentioned they are having the same problem and mine isn’t a problem so much as which do you think is better between solution A & solution B.
Thanks to all for replying
Given the constraints from your vendor then I think the delete proc you posted in your original post is your best option. I do not think you should try to have multiple try-catch blocks. You want this delete to be atomic. Either it can delete all the records or none of them. The template you defined initially looks correct and will accomplish what you want. The real beast is that you can't control somebody just deleting records from any of those tables so it is possible you can still end up with orphans. I think what you have proposed is about the best you are going to get. Not perfect but it works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 5, 2011 at 12:03 pm
Thanks for the input Sean
Kindest Regards,
Just say No to Facebook!Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply