VERSIONS: All
SUBJECT: Cascading delete
BY: STEVE CAPLE
Date:27 March 2000
QUESTION:
Can someone provide me with code to delete a record from a master table and its 6 dependent tables?
Assuming the tables are linked in the form's data model do this:
In the parent table uiObject (tableFrame or MRO) action() method,
Define a 'array[] string' with values set to the data model's table aliases
When the eventInfo.id() = DataDeleteRecord, check to see if NOT in edit mode (in that case doDefault)
siEventID = eventInfo.id()
switch
case siEventID = DataDeleteRecord:
if NOT isEdit() then
doDefault
else ;; ...
After suitable warning and confirmation [a msgQestion() dialog] delete the child tables like so:
;; warn & get confirm
;; if user gives go ahead then
;; step through the array with a for loop
for siIndex from 1 to aystChildTables.size()
tcTable.attach(aystChildTables[siIndex])
tcTableempty()
endfor
Then doDefault to delete the parent record, and resync the parent uiObject to the tCursor
CASE 2:
BY: BILL TODD, MARK PAUKER, and BRIAN BUSHAY
I need to write a deletion routine that will delete a master, all the detail records for that master, and all of the detail records for the detail
records. (Cascading delete two levels deep) I can handle one level deep through a forms data model but I haven't come up with a good way to go
two levels deep.
BY: BILL TODD
Is the second level detail table in the active form's data model? If so, attach a TCursor to the UIObject that displays it and do a tc.empty().
If not, open a TCursor on the second level detail table. Attach a TCursor to the first level detail table. Scan the first level TCursor, use
tc.setRange() on the second level table to restrict the view and use tc.empty().
BY: BRIAN BUSHAY
for the 3rd level attach a tcursor to the appropriate table it is easiest to use dmAttach() if the table is in the data model. The use setRange to set a
restricted range to the master table key values. Then if it is not a multi user app you can use Empty() otherwise deleterecord() in a for loop is
probably the easiest.
You can't assure all records are deleted. If another use has a record locked there is nothing you can do to unlock it. DeleteRecord() returns a
logical value so you can test its return code and if if fails abort the rest of the delete procedure.
BY: MARK PAUKER
A form's data model will cause the master to be locked as soon as a user attempts to lock a detail (thus allowing only 1 user to edit a master's
details at a time), but this is only enforced through the form, and thus the system is not secure.
As far as I know, there is no way to "safely" cascade a delete operation within Paradox.
CASE 3:
BY: BILL TODD
Date: 20 May 1997
QUESTION:
What are some approaches to handling cascading deletes, changes, etc,. Generally I use session lock, but it seems to me that this locks all the detail
table, instead of just the related records. What is the general thinking on this? I prefer not to use RI.
The best solution that I have found to cascade deletes is to use the Tcursor SetBatchOn and SetBatchOff methods as follows.
- SetBatchOn on the detail table.
- Loop through the detail records and lock each record.
- If you were able to lock each record loop through the records and delete each record.
- If you were able to lock each record loop through the records and delete each record.
- SetBatchOff.
If you find a record you cannot lock call SetBatchOff immeditely and issue an error message stating that some other user has one of the detail
records locked.
CASE 4:
BY : RICHARD DAVIS
Date: 6 August 1996
QUESTION:
When I delete the master record, I would like all the detail records for this master deleted also. How do I do this in either straight Paradox/Win
or in object Pal?
Below is some code that I use for this. You can modify it to suit your needs. In the action() event for the master record, I have the following
code as part of a switch statement. (ACCMAC is the linked child table.)
case eventInfo.id() = DataDeleteRecord and self.isEdit():
if msgQuestion("Confirm","Are you sure you want to delete this field definition?") = "Yes" then
if not ACCMAC.isEmpty() then
if msgQuestion("Confirm","There are macros assigned. Delete anyway?") = "Yes" then
delayScreenUpdates(Yes)
if DeleteChildren() then
dodefault
else ;;; Delete children failed
eventInfo.setErrorCode(UserError)
endif
delayScreenUpdates(No)
else ;;; User answered no to confirm prompt to delete children
eventInfo.setErrorCode(UserError)
endif
else ;;; Child table already empty
dodefault
endif
else ;;; User answered no to confirm prompt to delete record
eventInfo.setErrorCode(UserError)
endif
This method is a custom method declared on the master table/record object.
method DeleteChildren() Logical
var
continueL Logical
endvar
delayScreenUpdates(Yes)
continueL = TRUE
if continueL then
if not ACCMAC.isEmpty() then
if not ACCMAC.empty() then
errorShow("ERROR","Module " + C_MODULE + ": Unable to delete child records.")
continueL = FALSE
endif
endif
endif
return continueL
endmethod
CASE 5:
BY: BILL TODD
Date: 8 November 1997
Paradox 8 does not support cascaded deletes. There are two ways to cascade deletes.
- Lock the child table, delete the records, unlock the child table. The disadvantage to this is that it requires a table level lock which prevents
other users from changing other records for a short period of time.
- Use the TCursor setBatchOn method. Call setBatchOn, Try to lock each of the detail records, if all can be locked then delete all of the detail
records and call setBatchOff. This is less restrictive on other users but can only be used for a few detail tables and a few records because if you
keep setBatchOn for too long you will get timeout errors.
I assume that by "this method" you mean using TCursors and the setBatchOn() and setBatchOff() methods. The answer is yes as long is you do not
need to delete too many records from too many tables. The problem with setBatchOn is that it actually locks the PDOXUSRS.LCK file which
prevents other user from placing or releasing locks. You only want to do this for very brief periods. The recommended maximum is two seconds.
If you can delete all of the necessary records in less than two seconds then this should work. If not, the only other alternatives are table locks or
move to a database server that supports cascaded deletes or allows you to cascade deletes using triggers.