Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to script a delete on dependent tables...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stuckne1
Starting Member

USA
22 Posts

Posted - 11/01/2010 :  14:50:58  Show Profile  Reply with Quote
For example the two tables here...I'm want to have the related row deleted in the 2nd table if a row gets deleted in the first row.

USE [MyDB_Workstudy]
GO
/****** Object: Table [dbo].[Users] Script Date: 11/01/2010 13:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[parkId] [int] NOT NULL,
[firstName] [text] NULL,
[lastName] [text] NULL,
[email] [text] NULL,
[role_id] [int] NOT NULL,
[confidentialityAgreement] [text] NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[parkId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


USE [MyDB_Workstudy]
GO
/****** Object: Table [dbo].[UsersParkIdOnly] Script Date: 11/01/2010 13:47:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UsersParkIdOnly](
[id] [int] IDENTITY(1,1) NOT NULL,
[parkId] [int] NOT NULL
) ON [PRIMARY]


stuckne1
Starting Member

USA
22 Posts

Posted - 11/01/2010 :  14:51:46  Show Profile  Reply with Quote
For example the two tables here...I'm want to have the related row deleted in the 2nd table if a row gets deleted in the first table.*
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 11/01/2010 :  15:00:46  Show Profile  Reply with Quote
Either:

1) set up a Foreign Key with Cascade Delete
2) Set up a Trigger on the Parent record that deletes any Child records (but then you cannot have a foreign key)
3) Use a Stored procedure to delete records from Parent Table and have the SProc pre-delete any Child records (in a transaction block so that either the parent AND any children are deleted, or [if error / power-cut etc.] then neither are deleted.
Go to Top of Page

stuckne1
Starting Member

USA
22 Posts

Posted - 11/01/2010 :  15:33:55  Show Profile  Reply with Quote
Okay, I've been trying a variety of ways to use Cascading delete and I came up with this...but I have a few syntax errors...

USE [MyDB_Workstudy]
GO
/****** Object: Table [dbo].[Users] Script Date: 11/01/2010 14:29:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TABLE [dbo].[Users](
[parkId] [int] NOT NULL,
[firstName] [text] NULL,
[lastName] [text] NULL,
[email] [text] NULL,
[role_id] [int] NOT NULL,
[confidentialityAgreement] [text] NULL,
[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER table [dbo].[Users] ADD CONSTRAINT
(FOREIGN KEY (id) REFERENCES UsersParkIdOnly
ON DELETE CASCADE CONSTRAINT id)


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '('.
Go to Top of Page

stuckne1
Starting Member

USA
22 Posts

Posted - 11/01/2010 :  15:37:52  Show Profile  Reply with Quote
this is sql server 2005 is that makes a difference
Go to Top of Page

stuckne1
Starting Member

USA
22 Posts

Posted - 11/01/2010 :  15:59:27  Show Profile  Reply with Quote
never mind. Thanks!
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 11/01/2010 :  16:16:59  Show Profile  Reply with Quote
I should have said that I'm not that keen on Cascade Delete FKeys ...

If my application says

DELETE ParentTable WHERE ID=xxx

then a cascading foreign key will delete any children. But maybe I thought there were NO children but there was a bug in my program

Whereas if I just had an FKey (NO cascade delete) and I did the same Delete then I would get error "Child rows exist".

So I prefer, in my application, to decide that a particular delete operation should delete any children if they exist (maybe the user had to tick an extra checkbox, so there is an extra parameter to my SProc such as @ParamUserConfirmedChildDeletes that I use to conditionally pre-delete child records.

Either way, if something goes wrong in my application it is likely that it will fail on the side of a "Delete failed, child records exist" error message - rather tahn silently deleting data that I hadn't realised was there

Your call though, of course
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000