Cascade deleting

Cascade deleting

Post by drSzablo » Sat, 10 Feb 2001 05:43:48



Hi,

Is cascade deleting possible in MS SQL 7.0?
I assume it isn't, since before delete triggers are not available.

Bart

 
 
 

Cascade deleting

Post by Wojciech Galazk » Sat, 10 Feb 2001 06:14:55



Quote:>Hi,

>Is cascade deleting possible in MS SQL 7.0?

yes, using triggers

Quote:>I assume it isn't, since before delete triggers are not available.

you mean 'instead of' triggers? they were introduced in mssql 2000 for other
purposes ...

 
 
 

Cascade deleting

Post by drSzablo » Sat, 10 Feb 2001 21:02:17


Quote:> >Hi,

> >Is cascade deleting possible in MS SQL 7.0?
> yes, using triggers

Are you sure? Referential integrity constraints don't allow me to delete any
records from the table on 'one' side in 'one to many' relationship until all
of the related records from the 'many' side table are deleted.
Remember that the trigger in MS SQL 7.0 is executed after a succesful
completion of the delete statement.

Thanks and
"Pozdrawiam bardzo serdecznie :-)"
Bartek

 
 
 

Cascade deleting

Post by Wojciech Galazk » Sun, 11 Feb 2001 07:48:16



Quote:>> >Hi,

>> >Is cascade deleting possible in MS SQL 7.0?
>> yes, using triggers
>Are you sure? Referential integrity constraints don't allow me to delete
any
>records from the table on 'one' side in 'one to many' relationship until
all
>of the related records from the 'many' side table are deleted.

You didn't mention  referential integrity before (expressed with FOREIGN
KEY... REFERENCES ... constraint)

If referential integrity has been defined then it's not possible to use
'after' trigger for cascade deletes - a constraint takes action first and
rolls back the delete.
This is discussed  in Technet / SQL Server Resource Guide Part 3 - Planning
and Implementing Your SQL Server Solution /  Cascading Deletes/Updates with
Triggers, with sample code provided how to work over this wih MSSQL 6.5 /7.0

With MSSQL 2000 you'd rather want to use ON DELETE CASCADE constraint in
CREATE TABLE
Of course I may be wrong here as I don't have that much experience with
MSSQL 2000. Anybody could, please,  comment this on ?

Quote:>Remember that the trigger in MS SQL 7.0 is executed after a succesful
>completion of the delete statement.

Bezapelacyjnie :)

Quote:>Thanks and
>"Pozdrawiam bardzo serdecznie :-)"

I could not send the answer directly to you so I sent it here
 
 
 

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. DB design tool and the E-R diagram

3. Cascade Delete and Cascade Update on SQL 7

4. Clipper & Delphi

5. Cascading deletes, without cascade

6. 7.3.1 BREAKS COMPATIBILITY with the latest PHP4 (due to libpq.so.[23])

7. cascade delete vs delete transact sql

8. plpgsql

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

10. HELP! Creating delete trigger for cascade delete

11. Question about regular delete and cascading delete.

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

13. Referential Integrity VS Cascade Deletes