[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

DELETE. Need help. Thank you.

shapper

3/20/2007 10:23:00 AM

Hello,

I have 2 tables: Users and Documents.
Then I have many other tables which are related with FKs to Users and
Documents.

I want to delete all the dependent records on all tables when I delete
a document in Documents table. However, I don't want that to happen
when I delete a user in Users table.

How can I do this with CASCADE DELETE?

And should I use a trigger instead?

I am using SQL 2005.

Thanks,
Miguel
3 Answers

Tom Moreau

3/20/2007 10:29:00 AM

0

You would use ON DELETE CASCADE for those tables that depend on Documents.
You would use ON DELETE RESTRICT for those that depend on Users.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"shapper" <mdmoura@gmail.com> wrote in message
news:1174386199.707546.122540@l77g2000hsb.googlegroups.com...
Hello,

I have 2 tables: Users and Documents.
Then I have many other tables which are related with FKs to Users and
Documents.

I want to delete all the dependent records on all tables when I delete
a document in Documents table. However, I don't want that to happen
when I delete a user in Users table.

How can I do this with CASCADE DELETE?

And should I use a trigger instead?

I am using SQL 2005.

Thanks,
Miguel

shapper

3/20/2007 11:04:00 AM

0

On Mar 20, 10:28 am, "Tom Moreau" <t...@dont.spam.me.cips.ca> wrote:
> You would use ON DELETE CASCADE for those tables that depend on Documents.
> You would use ON DELETE RESTRICT for those that depend on Users.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ."shapper" <mdmo...@gmail.com> wrote in message
>
> news:1174386199.707546.122540@l77g2000hsb.googlegroups.com...
> Hello,
>
> I have 2 tables: Users and Documents.
> Then I have many other tables which are related with FKs to Users and
> Documents.
>
> I want to delete all the dependent records on all tables when I delete
> a document in Documents table. However, I don't want that to happen
> when I delete a user in Users table.
>
> How can I do this with CASCADE DELETE?
>
> And should I use a trigger instead?
>
> I am using SQL 2005.
>
> Thanks,
> Miguel

But the ON DELETE CASCADE and ON DELETE RESTRICT are table definitions
or should I place that in a stored procedure. I am not getting it.

But if a table have 2 FK: one related with documents and one to users.
If I delete a user I don't want the record to be deleted but if i
delete a document I want the record to be deleted.

Thanks,
Miguel

Hugo Kornelis

3/20/2007 6:33:00 PM

0

On 20 Mar 2007 04:04:24 -0700, shapper wrote:

(snip)
>But the ON DELETE CASCADE and ON DELETE RESTRICT are table definitions
>or should I place that in a stored procedure. I am not getting it.
>
>But if a table have 2 FK: one related with documents and one to users.
>If I delete a user I don't want the record to be deleted but if i
>delete a document I want the record to be deleted.

Hi Miguel,

They are part of the table definition. Or rather, part of the FOREIGN
KEY definition (which is, in turn, part of the table definition).

CREATE TABLE YourTable
(UserID int NOT NULL,
DocumentID int NOT NULL,
-- Other columns,
-- PRIMARY KEY (Some columns),
FOREIGN KEY (UserID) REFERENCES Users (UserID)
ON DELETE RESTRICT,
FOREIGN KEY (DocumentID) REFERENCES Documents (DocumentID)
ON DELETE CASCADE
);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...