Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-08 : 15:21:58
|
mark writes "SQL/Server 7.0 SP3 WinNT SP 6B
On the subject of cascading deletes, I have three tables defined in a hierarchical manner with declaritive referential integrity. Table one is the "Parent" of table two and table two is the "Parent" of table three. This works great for keeping the tables in "sync". However, on deletes I want to help out the application programmer and get rid of all records in table two whose parent is deleted in table one and all records in table three whose parents are deleted in table two. The problem is that when the delete comes through for table one the transaction abends (because of the referential integrity) before my trigger gets a chance to fire and delete the child records that shouldn't exist without their parent. There is another DBMS product whose name starts with the letter "O" who implement delete triggers with either a before delete or an after delete. With this product you could use the before delete trigger to delete the children and maintain integrity and then the actual delete occurs "AFTER" the trigger has fired. Is there an equivalent SQL/Server facility. (It's things like this that make me think Microsoft has a way to go before they catch up with that other vendor)! PS - I've already considered giving the programmer a procedure that would take the input param of the table one key and delete all children and the table one record, but that wouldn't be as transparent." |
|
|
|
|
|
Cascading Deletes (Yeah but more)