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


> > 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!
>
> Why not do all this in just one line of code?
> 
> Delete from data\CustOrdr where proj_key = liSourceProject

> There's a fine line between helping and adding to the confusion.
>
> How to create sample data

I tried that first, but since it did not work, the current code was just another approach to see if it made any difference (unfortunately, it didn't).

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