Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Cascade delete???
0 views
Skip to first unread message
Saleem Qamar
Oct 13, 1999, 3:00:00 AM10/13/99
to
I have two tables and I would like to delete an item from the main/parent
table and I want sqlserver to automatically delete all item relating to the
parent in the child tables. Please help
table and I want sqlserver to automatically delete all item relating to the
parent in the child tables. Please help
Sal
BPMargolin
Oct 13, 1999, 3:00:00 AM10/13/99
to
Saleem,
Please do not post to so many newsgroups. One or two is sufficient.
SQL Server does not support an automatic cascading delete. You will have to
program it yourself via triggers.
BTW, the word is that SQL Server 7.5 will (finally) support automatic
cascading delete.
Saleem Qamar <sqa...@home.com> wrote in message
news:eA8N3.748$A7.1...@news1.rdc1.on.wave.home.com...
Vladimir Evdokimov
Oct 14, 1999, 3:00:00 AM10/14/99
to
Hello Saleem,
Why don't you create a trigger on the parent table for delete? When you delete
from the table PARENT, it starts the trigger for delete, and you can analyze
the system table named "deleted" for fields you need (table "deleted" in
trigger on PARENT has the structure equal to table PARENT) and then delete (in
trigger) from table CHILD.
But remember that:
1) if you delete more than 1 record from PARENT by 1 query, you have to use
cursor on "deleted" table inside trigger (if it's not clear why please ask)
2)if you use FOREIGN KEY it will not works -- foreign key mechanism starts
before triggers. MicroSoft (OnLine books SQL 7.0) says that you can manage
referential integrity EITHER triggers, OR foreign key.
Why don't you create a trigger on the parent table for delete? When you delete
from the table PARENT, it starts the trigger for delete, and you can analyze
the system table named "deleted" for fields you need (table "deleted" in
trigger on PARENT has the structure equal to table PARENT) and then delete (in
trigger) from table CHILD.
But remember that:
1) if you delete more than 1 record from PARENT by 1 query, you have to use
cursor on "deleted" table inside trigger (if it's not clear why please ask)
2)if you use FOREIGN KEY it will not works -- foreign key mechanism starts
before triggers. MicroSoft (OnLine books SQL 7.0) says that you can manage
referential integrity EITHER triggers, OR foreign key.
Best Regards,
Vladimir Evdokimov
Sr. DBA "Alexander V. Kostikov and Partners" Ltd.
Scott Coutant
Oct 14, 1999, 3:00:00 AM10/14/99
to
If you are using a foreign jey, then the best option would be to do the
delete from a stored procedure. This would also be a faster option due to
the precompiling of stored procedures. Good Luck!
delete from a stored procedure. This would also be a faster option due to
the precompiling of stored procedures. Good Luck!
Vladimir Evdokimov <e...@avk.ru> wrote in message
news:38058C54...@avk.ru...
Charles Bretana Jr.
Oct 14, 1999, 3:00:00 AM10/14/99
to
One option is to Create a "DeleteObject" stored Proc, where you can
implement any business logic related to deletions, and delete all the
records from child tables in the appropriate order, inside of a
transaction.....
implement any business logic related to deletions, and delete all the
records from child tables in the appropriate order, inside of a
transaction.....
Regards,
Charly
Saleem Qamar <sqa...@home.com> wrote in message
news:eA8N3.748$A7.1...@news1.rdc1.on.wave.home.com...
0 new messages