Cascade Delete

Cascade Delete

Post by David L. Pento » Sat, 02 Mar 2002 12:50:51



Encapsulate your DELETE logic in a stored procedure.  You can do this in a
tranaction.

--
David L. Penton, SQL Server MVP
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"

Do you have the VBScript Docs or SQL BOL installed?  If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
SQL BOL: http://www.davidpenton.com/sqlbol


Quote:> I have about 20 tables that have FK relationships. I need
> to delete data from these related tables. We are using SQL
> server 6.5 and soon to move to SQL server 7.0. I see that
> Cascade deletes are available in SQL 2000, but we cannot
> move up this version now. I thought of putting triggers,
> but that would be too deep and likely to exceed the limit
> of 16 for 6.5 and 32 for 7.0 (because there are some
> circular relationships).

> Any thoughts as to how I can do this easily?

 
 
 

Cascade Delete

Post by BP Margoli » Sat, 02 Mar 2002 13:02:32


I'm going to make an assumption that the reason you have circular
relationships is because you are modelling hierarchical data.

May I suggest that you get hold of "Advanced Transact-SQL for SQL Server
2000" by Itzik Ben-Gan and Tom Moreau to see an alternative method to model
hierarchical data that may well solve your problem.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I have about 20 tables that have FK relationships. I need
> to delete data from these related tables. We are using SQL
> server 6.5 and soon to move to SQL server 7.0. I see that
> Cascade deletes are available in SQL 2000, but we cannot
> move up this version now. I thought of putting triggers,
> but that would be too deep and likely to exceed the limit
> of 16 for 6.5 and 32 for 7.0 (because there are some
> circular relationships).

> Any thoughts as to how I can do this easily?


 
 
 

1. cascade update , cascade delete

In MS Access I can create 2 tables  +  relation one to many
and to use option - enforce referential integrity,cascade delete related
fields
If  I wanted to do it in SQL6.5  I did not find any documentation about
it
I found only triger  , that can do something similiar , but It was
functional
only in this case if  don't  exist relation betewen tables

I created this trigger by myself and with help in MSDN
but it is not functional , pls  can anybody help to me ?
Can I know how to do the cascade delete on  2 tables
with relation

------------------------------------------------------------------------------------------

go
Create table TableA (id int NOT NULL primary key,name varchar(10) NOT
NULL)

go
Create table TableB (id int NOT NULL primary key ,
nl_id int not null references TableA(id),
name varchar(10) NOT NULL)

go
Insert into tableA values (1,'xxx')
Insert into tableB values (1,1,"xxx")

go
Create trigger TableA_trigger1 On TableA for delete as
delete TableB from TableB, deleted where TableB.id = deleted.id

go

delete from TableA
where id = 1
------------------------------------------------------------------------------------------

2. installing Oracle 8.1.7 on Windows XP

3. Cascade Delete and Cascade Update on SQL 7

4. IMPORTING ACCESS QUERIES AS SQL VIEWS

5. Cascading deletes, without cascade

6. collation conflict...HELP!!!

7. cascade delete vs delete transact sql

8. Processor Time Counter - Alert

9. delete trigger or cascade delete, which one??

10. HELP! Creating delete trigger for cascade delete

11. Multiple FK w/ CASCADE DELETE gives SQLERR: 274 no delete permission

12. Question about regular delete and cascading delete.

13. Cascade deleting