Cascade update and delete

Cascade update and delete

Post by proba.. » Wed, 23 Aug 2000 04:00:00



How to implement Cascade update and delete between parent and child SQL
7 tables?

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Cascade update and delete

Post by Brian Mora » Wed, 23 Aug 2000 04:00:00


you can't do this dri in SQL7. You can use triggers. SQL 2000 supports
cascase DRI constraints.

--
Brian Moran
CrossTier.com
MS SQL Server MVP
SQL Server Mag Columnist


> How to implement Cascade update and delete between parent and child SQL
> 7 tables?

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

Cascade update and delete

Post by Shawn Clar » Wed, 23 Aug 2000 04:00:00


Hi,

You will need to setup triggers on the tables that you want to use for
cascading deletes/updates. There is no built-in functionality to do this
automatically in SQL Server 7.0. When SQL Server 2000 is released they will
have built-in cascading updates/deletes.

An additional note is that if you want to be doing cascading deletes you
can't have foreign key relationships as the data is manipulated before the
trigger is fired. What I mean is if you had a parent record that was being
referenced from a child record. If you now wanted to delete the parent
record it would fail because there is the reference to the child record.
Even if you setup a trigger on the parent table to automatically delete the
child record it wouldn't help as the foreign key reference is checked before
the trigger is fired.

--
Shawn Clark
Senior Software Consultant
Meridian Technology Group


> How to implement Cascade update and delete between parent and child SQL
> 7 tables?

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

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. dao connection caching problem

3. Cascading updates and deletes with DB2 V.7

4. DAO 3.51 Security

5. cascading updates and deletes

6. sp_sdidebug????

7. Cascade Updates (and Deletes)

8. Atlanta: Oracle & Sybase DBAs

9. Using DRI and Cascade Updates and Deletes

10. Triggers: Cascading updates and Deletes

11. How do I Cascade Updates and Deletes

12. CASCADE UPDATE and DELETE

13. Cascade Updates And deletes