Deleting records

How do I delete one record from one table and cascade down all related tables?
Mike BKBA1 (http://support.microsoft.com/default.aspx?scid=kb;en-us;825021) & KBA2 (http://support.microsoft.com/?scid=kb;en-us;Q142480) to highlight issue if any trigger is involved.

This one too http://www.sqlteam.com/item.asp?ItemID=8595
HTH|||Establish cascading deletes between your tables, or use a trigger.|||Thanks for the replies guys, but apparently, I just have to learn patience. I am getting SQL Server 2000 at the end of the month and this supports Cascading DRI :).

Mike B|||Gotta be the same Mike

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33023

What about performance though..being "old school", I do it the old fashion way...|||What version are you using now? Hasn't cascading been a feature since at least 7.0, if not 6.5?|||Yeah...but you gotta remeber there's more than one platform...

DB2 OS/390, Oracle, SQL Server...oye

changing gears can get REAL interesting sometimes...

I guess I'm just from the school that here is no such thing as a key change...it's a new element...

and cascading deletes? I would imagine, is like giving a gun to a 2 year old for most developers...

no?

"OH SH-T, It's a MIRACLE! ALL THE DATA IS GONE!"

:eek:

Hey...triggers is hard enough for them to understand...

"Why are there 2 Rows affected?"|||Yeah, but my policy is not to design databases or write code for the lowest common denominator. If a business is too cheap to hire a competent DBA or Developer who can understand what I've built, then they deserve to have problems.|||Originally posted by blindman
What version are you using now? Hasn't cascading been a feature since at least 7.0, if not 6.5?
Update and Insert yes, not deleting

Mike B|||Originally posted by blindman
What version are you using now? Hasn't cascading been a feature since at least 7.0, if not 6.5?
6.5 never had it, neither did 7.0|||Fully agree,

As a developer I would never touch an application until I understood the data model and business rules defining it.

I find that triggered deletes too often get overlooked by developers, more so than the expectation that deletes WILL cascade throughout the data model.

Nothing worse than orphaned entries when trying to troubleshoot data errors.
Originally posted by MikeB_2k4
Update and Insert yes, not deleting

Mike B|||Originally posted by Brett Kaiser
Yeah...but you gotta remeber there's more than one platform...

DB2 OS/390, Oracle, SQL Server...oye

changing gears can get REAL interesting sometimes...

I guess I'm just from the school that here is no such thing as a key change...it's a new element...

and cascading deletes? I would imagine, is like giving a gun to a 2 year old for most developers...

no?

"OH SH-T, It's a MIRACLE! ALL THE DATA IS GONE!"

:eek:

Hey...triggers is hard enough for them to understand...

"Why are there 2 Rows affected?"

Extremely valid points!! (and yes it is the same Mike :) ) I am not sure if I even want to provide the deletion capability, but that doesn't mean I don't have to know how to do it.

Mike|||Originally posted by blindman
Yeah, but my policy is not to design databases or write code for the lowest common denominator. If a business is too cheap to hire a competent DBA or Developer who can understand what I've built, then they deserve to have problems.

What's the largest group you ever worked with?

And you know...it's always the database that's at fault...

Until you waste the time and prove them wrong (again)|||Yeah, and when you do, - they'll gang up on you and wait till it's really the database, and then you'll never hear the end of it... Hate them, hate them all!!!|||We have about 3K of them here, and only 5 of us...well, 2, if you count real bodies ;)|||Originally posted by rdjabarov
We have about 3K of them here, and only 5 of us...well, 2, if you count real bodies ;)

3,000? On a project level?

Or are you glass house support?|||Honestly, I don't know what 4/5 of them are here for, a mistery to me. But when on-call, I still have to be polite with them...at 3AM...when they trashed something, or when they can't connect and ask me to run a Profiler for them (that's the extent of their troubleshooting skills)|||I'll always remember getting an emergency call from the help desk once because a critical user couldn't get into the database. So I check the DB and everything is fine, and then I hustle down to her cube and find out that she has not electrical power to her station.

...but the case was a database issue because she couldn't get into her database... :rolleyes:|||You should embelish taht and send it in to the shark tank...|||Shark tank ?

-PatP|||oooh yeah...

You got to sign up and get the daily shark tank mailed to you...

http://www.computerworld.com/departments/opinions/sharktank?from=left|||I am firmly convinced that some of our vendors give their help-desk techs this as their script:

1) Are you sure the power cord is plugged in?
2) Have you applied the latest service pack?
3) Can you send us a copy of your database?

Hmm. Let's see. I have an application that works from one client, but does not work from another. Yeah, I can see as that is a database problem. Wait here, while I go get my troubleshooting baseball bat. ;-)|||That's not just any troubleshooting baseball bat...

It's a troubleshooting saux baseball bat!

And then there's April 16th in beantown! 8:05 prime time, national tv!

Maybe a ride up to visit the sister in law...

At least get a seat at the cask n' flaggon..

Still there?|||It's still there.

http://boston.citysearch.com/profile/4771681/

Who the heck runs this place? A Yankees fan?

Excerpt:

...and when the memories get too depressing, you can get a drink at the huge wraparound bar ....

Probably the most profitable bar in Boston ;-).|||I'm sitting here cracking up...and that doesn't look the flaggon I remeber...it was a dump...but then again it was late and I was hammered...

Is it still next to fenway?

EDIT: Or I could just click on the map...yup still there...

Another must visit place when I'm up is the beer works..well actually all the breweries...need new t shirts...

Deleting all rows from table with RI constraints

Hi
does sql server v2000 allow you to specify a delete with
cascade option similar to Oracle (oops) when removing all
rows from a given table or do you have to either drop the
constraints or remove data from the child tables prior to
the delete on the parent table? I do not want to use the
truncate command if possible.
Cheers
GCH| does sql server v2000 allow you to specify a delete with
| cascade option
--
Yes. In the Create Relationship dialog box, there is a checkbox labeled
"cascade delete related fields." This option "instructs the database to
delete corresponding rows from the foreign key table whenever rows from the
primary key table are deleted.
My reference: SQL Server Books Online
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Hello, GCH.
MS SQL Server 2000 suports ON DELETE CASCADE option.
Here is the example:
CREATE TABLE NewOrderDetails
(
OrderID int NOT NULL
CONSTRAINT FK1_NewOrderDetails REFERENCES Orders
(OrderID)
ON DELETE CASCADE
)
I hope this could help you.
"GCH" <anonymous@.discussions.microsoft.com> wrote in message
news:007f01c3bed2$4b982e60$a001280a@.phx.gbl...
> Hi
> does sql server v2000 allow you to specify a delete with
> cascade option similar to Oracle (oops) when removing all
> rows from a given table or do you have to either drop the
> constraints or remove data from the child tables prior to
> the delete on the parent table? I do not want to use the
> truncate command if possible.
> Cheers
> GCH

Delete trigger in SQL Server 7

Have gone through BOL and Google, but can't find the answer... please help
with a simple Q. I'm trying to create a simple cascade delete trigger in SQL
Server 7 where deleting "parent" records in table X delete corresponding
child records in table Y.

Table X
=========
X_ID
SOME_VAL

Table Y
=========
Y_ID
X_ID
SOME_VAL

When there is no relationship between X.X_ID and Y.X_ID, the following
trigger works fine:

CREATE TRIGGER "temp" ON x
FOR DELETE
AS

delete
from y
where x_id in (select x_id from deleted)

However, when a relationship is created to enforce referential integrity,
the trigger fails, with a "DELETE statement conflicted with COLUMN REFERENCE
constraint" error. I've seen examples where the trigger says (for example)
"AFTER INSERT", where presumably the code is specifically run after the
event that triggers it -- is there a way of forcing the trigger to run
before the delete from table X is executed? I've tried using "BEFORE
DELETE", but no dice :-\

Thanks!

--
Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI triggers
in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
k/html/sql_refintegrity.asp?frame=true>.

The main consideration is that you cannot have declarative referential
integrity in place when you perform cascading actions in triggers in SQL 7,
..

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aidan Whitehall" <aidanwhitehall@.fairbanks.co.uk> wrote in message
news:btph6b$kri$1@.sparta.btinternet.com...
> Have gone through BOL and Google, but can't find the answer... please help
> with a simple Q. I'm trying to create a simple cascade delete trigger in
SQL
> Server 7 where deleting "parent" records in table X delete corresponding
> child records in table Y.
> Table X
> =========
> X_ID
> SOME_VAL
> Table Y
> =========
> Y_ID
> X_ID
> SOME_VAL
>
> When there is no relationship between X.X_ID and Y.X_ID, the following
> trigger works fine:
> CREATE TRIGGER "temp" ON x
> FOR DELETE
> AS
> delete
> from y
> where x_id in (select x_id from deleted)
> However, when a relationship is created to enforce referential integrity,
> the trigger fails, with a "DELETE statement conflicted with COLUMN
REFERENCE
> constraint" error. I've seen examples where the trigger says (for example)
> "AFTER INSERT", where presumably the code is specifically run after the
> event that triggers it -- is there a way of forcing the trigger to run
> before the delete from table X is executed? I've tried using "BEFORE
> DELETE", but no dice :-\
>
> Thanks!
> --
> Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
> Macromedia ColdFusion Developer
> Fairbanks Environmental Ltd +44 (0)1695 51775|||> Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI
triggers
> in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
> k/html/sql_refintegrity.asp?frame=true>.

K, thanks -- I'll check that out.

> The main consideration is that you cannot have declarative referential
> integrity in place when you perform cascading actions in triggers in SQL
7,

Damn, damn, damn.

Well, thanks for the clarification, anyway.

Regards

--
Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775|||Aidan Whitehall wrote:

>>The main consideration is that you cannot have declarative referential
>>integrity in place when you perform cascading actions in triggers in SQL
> 7,
> Damn, damn, damn.
> Well, thanks for the clarification, anyway.

If I may request it ... would someone please confirm the above statement
about SQL Server. This statement is untrue is other commercial RDBMS
products, such as Oracle and DB2, and I would be surprised if SQL Server
didn't allow this very basic functionality. Is is still true in the
current version?

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||> If I may request it ... would someone please confirm the above statement
> about SQL Server. This statement is untrue is other commercial RDBMS
> products, such as Oracle and DB2, and I would be surprised if SQL Server
> didn't allow this very basic functionality. Is is still true in the
> current version?

One can implement cascading actions via DRI or INSTEAD OF triggers in
current version of SQL Server (SQL Server 2000).

Unfortunately, Aidan is using an older version (SQL 7) in which only the
AFTER trigger model is available. Consequently, it is necessary to enforce
referential integrity in triggers instead of DRI in cases when cascading
actions are performed in triggers. Another alternative is to perform
cascading functions via stored procedures.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Daniel Morgan" <damorgan@.x.washington.edu> wrote in message
news:1073846407.832632@.yasure...
> Aidan Whitehall wrote:
> >>The main consideration is that you cannot have declarative referential
> >>integrity in place when you perform cascading actions in triggers in SQL
> > 7,
> > Damn, damn, damn.
> > Well, thanks for the clarification, anyway.
> If I may request it ... would someone please confirm the above statement
> about SQL Server. This statement is untrue is other commercial RDBMS
> products, such as Oracle and DB2, and I would be surprised if SQL Server
> didn't allow this very basic functionality. Is is still true in the
> current version?
> Thanks.
> --
> Daniel Morgan
> http://www.outreach.washington.edu/...oad/oad_crs.asp
> http://www.outreach.washington.edu/...aoa/aoa_crs.asp
> damorgan@.x.washington.edu
> (replace 'x' with a 'u' to reply)|||Dan Guzman wrote:

>>If I may request it ... would someone please confirm the above statement
>>about SQL Server. This statement is untrue is other commercial RDBMS
>>products, such as Oracle and DB2, and I would be surprised if SQL Server
>>didn't allow this very basic functionality. Is is still true in the
>>current version?
>
> One can implement cascading actions via DRI or INSTEAD OF triggers in
> current version of SQL Server (SQL Server 2000).
> Unfortunately, Aidan is using an older version (SQL 7) in which only the
> AFTER trigger model is available. Consequently, it is necessary to enforce
> referential integrity in triggers instead of DRI in cases when cascading
> actions are performed in triggers. Another alternative is to perform
> cascading functions via stored procedures.

Thanks. I was a bit surprised that such basic functionality might not
exist. In Oracle one can always perform cascading deletes in any code
but the referential constraint, itself, will perform the function. For
example:

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

Thanks again for the clarification.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||> Thanks. I was a bit surprised that such basic functionality might not
> exist. In Oracle one can always perform cascading deletes in any code
> but the referential constraint, itself, will perform the function. For
> example:
> ALTER TABLE child
> ADD CONSTRAINT fk_child_parent
> FOREIGN KEY (test)
> REFERENCES parent (test)
> ON DELETE CASCADE;

The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)

BEGIN TRAN

CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
CREATE TABLE child(test int NOT NULL PRIMARY KEY)

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

insert into parent values(1)
insert into child values(1)

delete from parent
select * from parent --no rows
select * from child --no rows

ROLLBACK

--
Hope this helps.

Dan Guzman
SQL Server MVP|||Dan Guzman wrote:

>>Thanks. I was a bit surprised that such basic functionality might not
>>exist. In Oracle one can always perform cascading deletes in any code
>>but the referential constraint, itself, will perform the function. For
>>example:
>>
>>ALTER TABLE child
>>ADD CONSTRAINT fk_child_parent
>>FOREIGN KEY (test)
>>REFERENCES parent (test)
>>ON DELETE CASCADE;
>
> The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)
> BEGIN TRAN
> CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
> CREATE TABLE child(test int NOT NULL PRIMARY KEY)
> ALTER TABLE child
> ADD CONSTRAINT fk_child_parent
> FOREIGN KEY (test)
> REFERENCES parent (test)
> ON DELETE CASCADE;
> insert into parent values(1)
> insert into child values(1)
> delete from parent
> select * from parent --no rows
> select * from child --no rows
> ROLLBACK

Excellent. Thanks.

Like I said ... I would have been quite surprised if it didn't exist.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)