Cascading Deletes

When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.

Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?

thanks
dogNo, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).|||What!?!?!?

SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.

Triggers are NOT necessary for standard cascading.|||Cascade? Like a waterfall?

Has anyone scanned the landscape for a merry-go-round?

:D

Wholly disconnected ramblings bart man...

Seriously...be careful with cascading...should be no need...

I never liked messing with keys...

damn surrogates...

To me, if a key changes, then it's a new entity...or the key is defined improperly...

You lose all history...|||Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.

Disconneted ramblings...

...many...non-sequiturs...

Wish I had a key for elipsis so I didn't have to hit the period key three times...

Must...complete...sentence.... damn!|||Seriously...be careful with cascading...should be no need...

pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?|||Wow...dogmatic...

Cool...

You want to cascade...knock yourself out...|||SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
That seems to be the logical place for it, however the only options I have are:
Check existing data on creation
Enable relationship for INSERT and UPDATE
Enable relationship for replication

maybe a version difference :confused:|||You want to cascade...knock yourself out...
That's your solution?

WOW......
COOL DUDE.......
THANKS FOR THE MOST RIGHTOUS EXPLAINATION.....................
ATS AWESOME................|||Enable relationship for INSERT and UPDATE

Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.

Again, do I need to write triggers to accomplish my goal here :confused:

I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record :D|||See attached screenshot.|||Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.|||'bout time to upgrade, isn't it?|||cascade update\delete is "New" to sql 2000 v :eek:

and you dont have to create database devices anymore.. :D

cascade delete can not apply to two foreign keys

I have a table that contains two foreign keys of two different tables. I want to build a relationship so that when either primary keys deleted in the two tables, the record in the table should be deleted. But, SQL Server does not allow me to save the relationship, it complains that the circling delete might exist. I do not know why, how can I solve this?

Table A:
ID
ProductID <foreign key>
CustomerID <foreign key
Table Product
ProductID <primary key
Table Customer
CustomerID <primary key
I want to cascade delete the record in Table A when either the ProductID is deleted from Product table or the CustomerID is deleted from Customer table.In my opinion, relying on cascading updates and deletes isn't a good thing to do anyway. I think it's better to handle the deletes in your code when you explicitly want them to happen. I.e., the procedures to delete from either the product or customer table should include a delete statement against Table A. I don't think cascading deletes are a good idea to replace your own logic.

Capture Primary Key Violation Error

Hello,,

I need to capture the primary key violation error:

If e.CommandName = "Insert" Then
Dim EmployeeIDTextBox As TextBox = CType(dvContact.FindControl("EmployeeIDTextBox"), TextBox)
Dim LastName As TextBox = CType(dvContact.FindControl("LastName"), TextBox)
Dim FirstName As TextBox = CType(dvContact.FindControl("FirstName"), TextBox)

Using cmdAdd As New System.Data.SqlClient.SqlCommand

'Establish connection to the database connection
Dim sqlcon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("eConnString").ToString)

'Open connection
sqlcon.Open()

'Pass opened connection (see above) to the command object
cmdAdd.Connection = sqlcon

'Using "With/End With" pass content to columns from text objects and datatime variables (see above)
With cmdAdd
.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", EmployeeIDTextBox.Text))
.Parameters.Add(New SqlClient.SqlParameter("@.LastName", LastName.Text))
.Parameters.Add(New SqlClient.SqlParameter("@.FirstName", FirstName.Text))

'Establish the type of commandy object
.CommandType = CommandType.Text

'Pass the Update nonquery statement to the commandText object previously instantiated
.CommandText = "INSERT INTO ATTEmployee(EmployeeID, LastName, FirstName & _
"VALUES (@.EmployeeID, @.LastName, @.FirstName)"
End With

'Execute the nonquerry via the command object
cmdAdd.ExecuteNonQuery() '<==Need to capture primaryKey violation, give user message, cancel insert,return to detailView ReadOnly

'I haven't figured out the correct code to capture the primary key violation

EDITMsg.Text="You can not insert an duplicate record. Try Again."

'Close the sql connection
sqlcon.Close()
End Using
End If

Thank you for your help

Just wrap the ExecuteNonQuery call in a try block, then catch the SqlException. that object has a lot of information about all the errors that come from SQL Server.

Make sense?

Don

|||

No,

I tried to write Try,,Catch,,End Try, but it did not work. I got errors with the using/end using.

|||

Oh! You said you wanted to trap errors on the ExecuteNonQuery. So let's take this one thing at a time and look at using.

First, there really is no reason to use using with a SqlCommand object, since it is a managed resource, although it uses a connection. Better to use it with the SqlConnection object, which definitely needs closing when you're done with it.

That said, what errors do you get with the using? But it doesn't really matter at this point.

Don

|||

I do need to trap the error on the ExecuteNonquery.

Let me start again, I need to capture the Primary Key Violation Error, write a message, i.e. ErrorMsg.Text = "You have an error...".

|||

Okay, then something like this should do it:

Try
cmdAdd.ExecuteNonQuery()
Catch SqlException as sqlEx
' Do something here
Catch 'other exeptions if you need to
End Try

What you do with the exception depends on what you want to present to the UI. You'll normally want to use the exception object's Errors property to get a collection of SqlError objects with the details of what SQL Server sent about the error, and there may be more than one error. You can handle the primary key exception this way.

You also need to manage the connection to the database. It remains open if the error severity is 19 or less.

Does this make sense? Is this what you tried and said didn't work?

Don

|||

I tried the "Try-Catch-End Try" and it is not working nor has not worked for me..I shows are error when there is a duplicate record and when there I try to insert a new record.

My snipet:
<asp:DetailsViewID="dvContact"DataKeyNames="EmployeeID"DataSourceID="sqlEmployeeByID"Height="346px"Width="385px" OnItemInserted="Display_Insert_Msg"
<InsertItemTemplate>
<asp:ButtonID="Button8"CommandName="Insert"Text="Insert"runat="server"Font-Size="10pt"Width="50px"/>
<asp:ButtonID="Button9"CommandName="Cancel"Text="Cancel"runat="server"Font-Size="10pt"Width="50px"/>
</InsertItemTemplate>

Along with the above I have:

Protected Sub Display_Insert_Msg(ByVal sender As Object, ByVal e As DetailsViewInsertedEventArgs)

If e.Exception IsNot Nothing Then
'Error in the new inserted data value.
'Display error message.
' ErrorMessage.Visible = True
EditMSG.Text = "• Duplicate Employee ID Numbers not allowed." <<=== This line writes to the browser when there is a new record and a duplicate record.
e.ExceptionHandled = True
e.KeepInInsertMode = True
Else
'Update the FormView control display
'to reflect a product row insertion.
EditMSG.Text = String.Empty
dvContact.DataBind()
grdEmployees.DataBind()
End If

End Sub

Now, with the Try-Catch-End

Try
'Execute the nonquerry via the command object
cmdAddEmployee.ExecuteNonQuery()
Catch Sqlex As System.Exception
lblErrorMsg.Text = Sqlex.ToString '& ex.Number
End Try