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

carry over values to next page

hi, all.
is it possible to carry-over a subtotal of a page to the page header of the
next page?
we use ssrs 2005.
thx,
Carstenhi, all.
no idea? am i the one and only who needs a carry-forward value?
pls help.

Carriage return in header of Flat File Destination

I'm trying to create a flat file that has a header like:

/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100
data,data,data,data
data,data,data,data

where 'data' represents the data written out by the data flow process to the flat file destination. This actually turns out quite nice except that when I place the lines that start with '/' in the header box for the flat file destination the carriage return doesn't get written correctly after each line and I end up with an unrecognized character when I open the file in a simple app like notepad. I've tried using different encodings for the flat file connection, but to no avail. It is also interesting to note that when I close the package and reopen it the flat file destination editor UI also doesn't recognize the carriage returns and places a box in there place.

Below is a copy of the the property as it is written in the package xml:

<property id="92" name="Header" dataType="System.String" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100</property>

Any help is appreciated.

-dotnetwiz

I was able to do this using a property expression on the 'header' property (accessed via expressions of the dataflow task).
When I reversed the order of \r\n to \n\r I do get some messages about inconsistent line delimeters in some editors.

"/INST=-1\r\n"
+"/DELIMITER=\",\"\r\n"
+"/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4\r\n"
+"/LOCATION=100\r\n"

Hope this helps

|||How do you get to the "Expression" of the DataFlow task? Right-click does not list "expressions" as a menu item. The Advanced Editor does not provide any apparent access to "Expressions"...?|||In the control flow, right-click on the data flow task and select properties. Scroll down in that list and you'll see "Expressions."|||I'm trying to do something similar in setting up a header for a fixed width flat-file output. When I try to use \r\n after my text, the characters "\r\n" just show up in the header. How do I get a CR+LF? I've tried using ="mytext\r\n" and I just see that entire literal string, including the quotes, appear in the output.|||As Phil pointed out, on the Control Flow tab, you have a DataFlow component (which, when you edit it, leads to the DataFlow tab and displays components there). If you look at the properties for the object on the control tab, one of them is "Expressions" and you can open it to get at the properties of the components on the Dataflow tab (like header for a flat file destination.)

Setting the Expression to a quoted string allows you to include \r\n and they will be translated properly.

Carriage return in header of Flat File Destination

I'm trying to create a flat file that has a header like:

/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100
data,data,data,data
data,data,data,data

where 'data' represents the data written out by the data flow process to the flat file destination. This actually turns out quite nice except that when I place the lines that start with '/' in the header box for the flat file destination the carriage return doesn't get written correctly after each line and I end up with an unrecognized character when I open the file in a simple app like notepad. I've tried using different encodings for the flat file connection, but to no avail. It is also interesting to note that when I close the package and reopen it the flat file destination editor UI also doesn't recognize the carriage returns and places a box in there place.

Below is a copy of the the property as it is written in the package xml:

<property id="92" name="Header" dataType="System.String" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100</property>

Any help is appreciated.

-dotnetwiz

I was able to do this using a property expression on the 'header' property (accessed via expressions of the dataflow task).
When I reversed the order of \r\n to \n\r I do get some messages about inconsistent line delimeters in some editors.

"/INST=-1\r\n"
+"/DELIMITER=\",\"\r\n"
+"/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4\r\n"
+"/LOCATION=100\r\n"

Hope this helps

|||How do you get to the "Expression" of the DataFlow task? Right-click does not list "expressions" as a menu item. The Advanced Editor does not provide any apparent access to "Expressions"...?|||In the control flow, right-click on the data flow task and select properties. Scroll down in that list and you'll see "Expressions."|||I'm trying to do something similar in setting up a header for a fixed width flat-file output. When I try to use \r\n after my text, the characters "\r\n" just show up in the header. How do I get a CR+LF? I've tried using ="mytext\r\n" and I just see that entire literal string, including the quotes, appear in the output.|||As Phil pointed out, on the Control Flow tab, you have a DataFlow component (which, when you edit it, leads to the DataFlow tab and displays components there). If you look at the properties for the object on the control tab, one of them is "Expressions" and you can open it to get at the properties of the components on the Dataflow tab (like header for a flat file destination.)

Setting the Expression to a quoted string allows you to include \r\n and they will be translated properly.