Cascade Updates and Deletes

[Randy]

It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in

[Randy]

...but it is included in SQL2000. Since I am coming from an Access environment, what combination of things can I use to create a cascaded update or delete in relationships in SQL7? Thanks,
Randy

------------
Randy at 2/27/01 4:11:20 PM


It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in

[Kelsey]

You will need to write triggers on the tables. Check BOL.

Kelsey

------------
Randy at 2/27/01 4:14:21 PM

...but it is included in SQL2000. Since I am coming from an Access environment, what combination of things can I use to create a cascaded update or delete in relationships in SQL7? Thanks,
Randy

------------
Randy at 2/27/01 4:11:20 PM


It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in

[Dirk]

Randy,
as an alternative to the standard primarykey-foreignkey scheme from SQL7, you can enforce the referential integrity completely
by triggers. So you should have insert-triggers in the child-tables to ensure
that their foreign keys exist as primary keys (or whatever) in the parent-table.
Then, you can add a delete trigger in the Parent-table that performs the cascading delete effect you wanted. It's a bit more work but it gets the job done.
Just a small not on the side: if you intend to use this database in a merge-replication, be sure to include the "Not For Replication" clause in your triggers.

Dirk

------------
Randy at 2/27/01 4:14:21 PM

...but it is included in SQL2000. Since I am coming from an Access environment, what combination of things can I use to create a cascaded update or delete in relationships in SQL7? Thanks,
Randy

------------
Randy at 2/27/01 4:11:20 PM


It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in

[Marc Scheuner]

Implementing referential integrity by using triggers is a kludge and should not be considered a good practice!

Coming from Oracle, I was stumped to see that MS SQL Server 7 didn't support such basics as full declarative referential integrity
(with cascading deletes and updates), and also lags behind Oracle in things like "BEFORE" and "AFTER" triggers - if SQL 7 at
least would have "BEFORE DELETE" triggers, then yes, you could use triggers to implement cascading deletes.

RIght now, your only real options are to either write a stored proc that will delete your child records before it deletes the
parent record, or then go to SQL Server 2000 right away.

Marc


------------
Dirk at 3/2/01 3:38:50 AM

Randy,
as an alternative to the standard primarykey-foreignkey scheme from SQL7, you can enforce the referential integrity completely
by triggers. So you should have insert-triggers in the child-tables to ensure
that their foreign keys exist as primary keys (or whatever) in the parent-table.
Then, you can add a delete trigger in the Parent-table that performs the cascading delete effect you wanted. It's a bit more work but it gets the job done.
Just a small not on the side: if you intend to use this database in a merge-replication, be sure to include the "Not For Replication" clause in your triggers.

Dirk

------------
Randy at 2/27/01 4:14:21 PM

...but it is included in SQL2000. Since I am coming from an Access environment, what combination of things can I use to create a cascaded update or delete in relationships in SQL7? Thanks,
Randy

------------
Randy at 2/27/01 4:11:20 PM


It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in