Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: David Mustakim
  Where is David Mustakim?
 Jakarta
 Indonesia
 David Mustakim
 To: Jim Carls
  Where is Jim Carls?
 Nashville
 Tennessee - United States
 Jim Carls
 Tags
Subject: RE: Cascading deletes not deleting
Thread ID: 395635 Message ID: 395639 # Views: 39 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, January 16, 2014 4:28:15 PM         
   


CMIIW but my understanding based on actual use on my accounting application the RI-"cascading" applies to one relation as specifically defined between two tables. It does not cascade further down to grand-child tables ( see Help no mention of the term "grand-" ... but tonight I'd probably testing it as I didn't really use it for anymore than 1 level)

David


> I'm having an odd problem with a utility I'm writing that archives then deletes all the records related to a construction project. Because of the way the database evolved over the years, the referential integrity splits between a project-oriented "design" part of the database and a purchasing-oriented part. This is how it is set up:
>
> Design tables: cascading deletes when the project record is deleted.
> However, delete of the project record is restricted if project orders, project invoices or project check requests exist.
> Deletion of invoices and check requests are restricted if order item splits exist that reference them.
> Deletion of an order cascades the deletes to the order items and the order item splits.
>
> To do the deletions, I have a loop for each project being deleted:
> It creates a transaction, then deletes the project's purchase orders, which should cascade to the items & splits.
> Then deletes the project's invoice records.
> Then the check request records.
> Then it deletes the project record, which cascades to the "design" tables.
> It then ends the transaction.
>
> This process seems to work fine in the development system, but when I run it on the client database for which I'm creating it, I get a trigger failure on a particular project when it attempts to delete the project record. In looking at the resulting data, I find that all the "design" table children of the project have been deleted, but the orders, order items and item splits are still there. Worse, I find that the orders for the previously deleted projects are also still there, but they have been orphaned.
>
> I considered whether there was a corruption in the database itself, but one of the steps in getting to this point was to import the entire database into a new empty database copied from the development system. I'm going to be trying new tests, but are there any "gotchas" that I might have missed with a cleanup procedure like this? Any suggestions as to why an explicit line like this:
>
>
>
> BEGIN TRANSACTION
> USE data\CustOrdr ORDER Proj_Key
> IF SEEK(liSourceProject)
> 	SCAN WHILE Proj_Key = liSourceProject
> 		DELETE
> 		* Relational integrity deletes children
> 	ENDSCAN
> ENDIF
> USE
> 
> ...and so on...
> 

>
> ...would fail to delete the orders with liSourceProject in the key? Thanks!

ENTIRE THREAD

Cascading deletes not deleting Posted by Jim Carls @ 1/16/2014 3:51:01 PM
RE: Cascading deletes not deleting Posted by Tore Bleken @ 1/16/2014 3:57:14 PM
RE: Cascading deletes not deleting Posted by Jim Carls @ 1/16/2014 4:45:33 PM
RE: Cascading deletes not deleting Posted by Tore Bleken @ 1/16/2014 5:45:53 PM
RE: Cascading deletes not deleting Posted by David Mustakim @ 1/16/2014 4:28:15 PM
RE: Cascading deletes not deleting Posted by Jim Carls @ 1/16/2014 4:50:20 PM
RE: Cascading deletes not deleting Posted by David Mustakim @ 1/16/2014 5:00:03 PM
RE: Cascading deletes not deleting Posted by tom knauf @ 1/16/2014 5:15:43 PM
RE: Cascading deletes not deleting Posted by David Mustakim @ 1/16/2014 5:24:05 PM