SQL Server Forums
Profile | Register | 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...
 New Topic  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
22415 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
22415 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000