February 20, 2008 at 2:24 pm
I have 2 tables, ID in 'TABLE A' COULD be referenced in 'TABLE B'. If a record is deleted in 'TABLE A' I would like to have the DB automatically delete any records in 'TABLE B' if the related fields values match. Problem is that reference value in 'TABLE B' could not be related to 'TABLE A's ID.
Is there any type of relationship that can support this type of cascade delete?
in this example if you delete the second record from TABLE A I would like 4 and 6 from TABLE B to auto delete
TABLE A
ID, name
1, name1
2, name2
TABLE B
ID, tableA_ID, tableC_ID
3, 0, 2
4, 2, 0
5, 1, 0
6, 2, 0
7, 0, 1
Thanks for any assistance!
February 20, 2008 at 4:13 pm
You could create a foreign key constraint between the two tables and enable the delete cascade. As a best practice, I would not recommned going down this route. Instead, run your deletes in the correct order within a transaction.
By the way, it looks like table B may have related data in table C as well. You should account for this as needed.
SET NOCOUNT ON
DECLARE @TABLEA TABLE (ID int, name varchar(10))
INSERT INTO @TableA
SELECT 1, 'name1' UNION ALL
SELECT 2, 'name2'
DECLARE @TABLEB TABLE (ID int, tableA_ID int, tableC_ID int)
INSERT INTO @TableB
SELECT 3, 0, 2 UNION ALL
SELECT 4, 2, 0 UNION ALL
SELECT 5, 1, 0 UNION ALL
SELECT 6, 2, 0 UNION ALL
SELECT 7, 0, 1
SELECT * FROM @TableA
SELECT * From @TableB
DECLARE @RowToDelete int
SET @RowToDelete = 2
BEGIN TRANSACTION
DELETE b
FROM @TableB b
INNER JOIN @TableA a
ON b.TableA_ID = a.ID
WHERE a.ID = @RowToDelete
DELETE @TableA
WHERE ID = @RowToDELETE
COMMIT TRANSACTION
SELECT * FROM @TableA
SELECT * From @TableB
By the way, this code does not contain error handling as it should.
February 20, 2008 at 6:07 pm
As John mentioned - Cascade delete entails having a foreign key constraint (which means that every record in the child table MUST be related to something in the parent). So - this will not work in your case.
For what it's worth - that also means you have some type of normalization issue going on, which should probably need to be fixed and closed off. That has "orphan records" written all over it.
You might be better off with a DELETE trigger on the parent table to clean out the child if the records match.
I leverage cascade delete on occasion - but it's there to be the "backup" to any other cleanup process I have.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2008 at 12:23 pm
SSCrazy, I'm rather green as to some of the deeper aspects of SQL Server. Can you give me a quick 1,2,3 (or point to a resource) on implementing a delete trigger on a table?.
Thanks for the reply!
February 21, 2008 at 12:50 pm
(By the way - My name is Matt - "SSCrazy" is a "level", not a name)
First - you should read up a bit on Triggers. They're essentially the same as what it means in a lot of other programming scenarios: an event occurs, which can then be set up to set off one or more triggers.
There are lots of kinds of events in 2005. On tables - among others - you have INSERT, UPDATE and DELETE triggers, meaning - they trigger when an activity deletes/inserts/updates rows. The big thing to remember is that the trigger fires once per batch/activity (meaning if you have a delete query killing 200 rows, the trigger fires ONCE, but "knows" about 200 rows being killed off).
You can also hang the trigger "before" the activity (what is called an INSTEAD OF trigger), possibly allowing you to prevent the activity, or AFTER (an AFTER or FOR trigger).
When I say it "knows" - SQL Server gives you access during these transactions to two "virtual tables", called inserted and deleted which hold what was there "before" (deleted") and what will be there AFTER (inserted). They're virtual in that they don't exist outside of the scope of this trigger.
So - in an UPDATE, inserted would hold the "new values", deleted would hold the "old" values.
Anyway - there's a LOT to read about triggers. I would start by hitting BOL:
http://msdn2.microsoft.com/en-us/library/ms189799.aspx
So... Taking this back to your specific example, your trigger would look something like:
ALTER trigger [dbo].[tg_TableA_del] on [dbo].[TableA]
for Delete
as
Begin
delete from TableB
where TableA_ID in
(select ID from deleted) --deleted has all of the rows that were deleted from tableA
end
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 3:48 pm
Matt, I am totally sorry about the SSCrazy thing. 😀
I am obviously new to this forum.
Anyway thanks for the info. I will dig into it further and give myself an education.
I appreciate your time, Thanks!
February 28, 2008 at 4:13 pm
Not an issue - welcome to the boards!
Let us know if you get stuck. Lots of highly competent folks on here, much more so than I.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 4:30 pm
Matt, You're being modest!
😎
February 28, 2008 at 6:07 pm
Not at all - t'is true. I do okay - but there are lots here who can run circles around me day in and day out.
's okay - I've got a good memory, and can usually figure out how to reapply what's been done. I just like to be around when the new tricks pop up...
That being said - thank you for the vote of confidence!!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 28, 2008 at 6:18 pm
Matt is right that Triggers are the way to get exactly what you are describing, but have you considered restructuring the data so that the relationship always exists?
That would let you use the foreign key and cascading delete which is more efficient than the trigger.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 29, 2008 at 7:42 am
timothyawiseman,
I am no SQL pro but it seems that (at least in my specific instance) any other schema would require quite a bit more db management programatically. I can't say if it's considered 'best practice' or not but If I can solve this issue at the DB level that would save me a whole lot of time writing additional code.
Thanks for the input.
February 29, 2008 at 7:45 am
That certainly makes sense. If ensuring that there is always a matching row requires a great deal of work, then triggers are probably the way to go for you.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 12, 2008 at 3:20 am
Hi,
Please help to how to delete record inside a table where parent-Child realtion exits in single table.
CREATE TABLE [dbo].[Menu](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[Text] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentID] [int] NULL,
[LinkTo] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sequence] [int] NULL
)
This is table strucute
insert into menu values(1,'test','test',null,1)
insert into menu values(2,'test','test',1,1)
insert into menu values(3,'test','test',1,1)
insert into menu values(4,'test','test',3,1)
insert into menu values(5,'test','test',4,1)
So, Menu table contain parentid-relation with Id.
So, i need a delete statement to delete record. If sub-Item is delete it will automtically delete all there parent.
If not clear let me know.
thnks
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply