Problem with cascading deletion (circular)

Problem with cascading deletion (circular)

Hi, I am reletively new to DB programming and have come across a fairly poorly designed DB. I am trying to set up cascating deletes and triggers in such a way that if the application deletes an entry in Table A then all the associate data in table B through H will also be deleted. However because of SQL Server 2005 restrictions on Cascading deletes and the cycles in the involved tables I have not been able to do it. Does anyone have some tips or suggestions?

Here is the basic design:

Table A
Table B
Table C
Table D
Table E
Table F
Table G
Table H

Relationships

1) Table B --> Table A
2) Table C --> Table B
3) Table D --> Table B
4) Table E --> Table C
5) Table E --> Table D
6) Table F --> Table E
7) Table F --> Table G
8) Table H --> Table G
9) Table H --> Table B

I can get cascading deletes for relationships 1 to 4 but once I try 5 it
recognizes the B-C-D-E cycle.

Circular cascading deletes are not allowed.

You may be able to use triggers instead.

FROM The Books Online topic:

Cascading Referential Integrity Constraints

Multiple Cascading Actions

Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables: TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.

I think the Advanced Transact SQL Book by Tom Moreau and Itzik Ben-Gan has some design info around this.

Problem with " , " and " "

I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?whether you use stored procedures or straight sql instructions from a VB client, you still need to pass parameters and if you need to pass a string parameter that contains a single quote (') insert another quote just next to it and it should be fine. As for commas, a string parameter containing a comma and delimited by two single quotes should work fine.

try in QA:

create table #temp(field1 varchar(500))
insert into #temp(field1) values ('test1''')
insert into #temp(field1) values ('test2 ,')
select * from #temp
drop table #temp|||...but use stored procedures anyway...|||is it nececessary to user storedprocedures for data inserting?
cant we use

sSql = "insert into tablename values (" & var1 & "," & var2 & ")"
dbConn.Execute sSql

cud u pl tell, if thers ne advantage in using sp for data insertion|||Using an sp for data insertion can have the advantage of shielding the db layout from applications, so applications may not have to be modified, recompiled and distributed if changes are made. Some database administrators like to know all the update/insert statements that could be executed so they can tune the database. It might also help seperating business logic from your applications.

I'm sure there are more, but these are the ones I can come up with.

One thing I haven't mentioned is that the company you work for may have chosen for one type of approach (having all in vb or all in sp), which sort of overrules advantage/disadvantage.|||apart from separating the business logic from applications, will there be an improved performance for large insert/update statements while using an sp

i.e, for an insert statement like

sSql = "insert into table1(field1,field2,....fieldn) values ("
& val1 & "," & val2 & "," .... & "," & valn & ")"
dbConn.Execute sSql

pl post ur comments|||I'm programming under VB, and I have a connection to a MSSQL Server 2000 database. How can I make a query work when a string contains "," and "'"? All I could think about is changing all querys to stored procedures. Is there any special character I could use to tell the server to include the coma as part of the string?

you can use this code to sole ur Problem

Pvar_DataBase.Execute "insert into " & TablName _
& "(Filed01,Filed02)" _
& " Values('" & value01 & "','" & Single_Qute(Value02) & "')"

Public Static Function Single_Qute(String_Value As String) As String
Single_Qute= Replace(String_Value, "'", "''")
Single_Qute= Replace(String_Value, ",", "''")
End Function

If u have any problem

send me to
tgamil@.egysoft-it.com

Best Regards

Tarek Gamil

Problem w/ SQL Server Database Wizard

I'm relatively new to VB.NET and Database programming, so I don't know a WHOLE lot about both, but I've recently purchased Wrox's "Beginning Visual Basic .NET Databases" and I've run into a snag on page 48.

The text says to fill in the Login ID and Password ("sa" and blank) on the first screen of the Microsoft SQL Server Database Wizard in Access, but when I do, I get an Overflow error. I get the same error if I try to Use Trusted Connection. I can't get past this screen.

I'm running Access 2000, and the SQL Desktop Engine 2000, and I don't have Enterprise Manager. Anyone know how to help me?

Please!!! Help me!!!Do you have latest service packs on SQL SErver and OFfice tools?|||No, I don't. Would I get those from Microsoft's site? (Damn, I hate navigating that website.)|||Hey, Satya, service pack 3 did the trick.

Thanks for the help.

Sean