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: tom knauf
  Where is tom knauf?
 
 Germany
 tom knauf
 Tags
Subject: RE: Cascading deletes not deleting
Thread ID: 395635 Message ID: 395649 # Views: 34 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, January 16, 2014 5:24:05 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!
>
> Hello,
>
> If I remember correctly, VFP does not have a cascaded delete, it must be made with triggers.
> If I understand you right : you appended the cust. db into a blank copy of your db , on cust. system.
> So the triggers in cust. db may be different from you dev. database.
> Can you for test copy the cust. dbc to your dev system to check/try triggers ?
>
> regards
> tom

Cascade delete exists Tom


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