The purpose of this post is to start a dialog that will, hopefully, lead to a better understanding of the way to handle deletion of multiple records from multiple inter-related tables in a multi-user environment.
Here is a diagram of one such (simplified) senerio:
I should mention that the forms that the parent tables are associated with also have embedded browses on them. The child tables are represented on these forms as embedded browses. (each parent and it's two children on one form)
The senerio is as follows. When you delete a record from parent 1, there will (sometimes) be a corresponding record in parent 2 that will also need to be deleted. In addition, all of the corresponding child records will also need to be deleted. I need to make sure that ALL of the records are deleted. There can be no orphans or corrupted relations.
This is one approach I have been kicking around. I would like to hear feed back on this approach. Is it a good solution? Bad solution? Pros, cons? Alternate solutions?
The solution above is not 100% complete. Some of the error checking was omitted intentionally.
This script works by locking a single record in each parent table.
The parent tables are related (not a set) to eachother. They both have a field that is used to correlate records between them.
The thought behind this is that if I am able to lock both parent records at once then all of the related children should also be locked. Is this assumption correct?
I look forward to hearing your thoughts...
Here is a diagram of one such (simplified) senerio:
I should mention that the forms that the parent tables are associated with also have embedded browses on them. The child tables are represented on these forms as embedded browses. (each parent and it's two children on one form)
The senerio is as follows. When you delete a record from parent 1, there will (sometimes) be a corresponding record in parent 2 that will also need to be deleted. In addition, all of the corresponding child records will also need to be deleted. I need to make sure that ALL of the records are deleted. There can be no orphans or corrupted relations.
This is one approach I have been kicking around. I would like to hear feed back on this approach. Is it a good solution? Bad solution? Pros, cons? Alternate solutions?
Code:
[COLOR=magenta]option strict[/COLOR] [COLOR=blue]' Array of pointers to reference the tables[/COLOR] [COLOR=magenta]DIM tbl[6] as P [/COLOR][COLOR=blue]' Array of pointers to reference the queries [/COLOR][COLOR=magenta]DIM qry[6] as P[/COLOR] [COLOR=blue]' Array to store the number of records that match each queries[/COLOR] [COLOR=magenta]DIM rec_cnt[6] as N[/COLOR] [COLOR=blue]' Index for while loops[/COLOR] [COLOR=magenta]DIM i as N [/COLOR] [COLOR=blue]' Populate the array with pointers to each table[/COLOR] tbl[1] = table.open("parent 1 child 1") tbl[2] = table.open("parent 1 child 2") tbl[3] = table.open("parent 2 child 1") tbl[4] = table.open("parent 2 child 2") tbl[5] = table.open("parent table 1") tbl[6] = table.open("parent table 2") [COLOR=blue]' Query all of the tables to get the records that need to be deleted ' Assume vFilter contains the proper filter expressions for each table ' Also get the number of records in each query[/COLOR] i = 1 WHILE (i <= 6) qry[i] = tbl[i].Query_Create("",vFilter) numrecs[i] = qry[1].records_get() i = i+1 END WHILE [COLOR=blue]' Lock the parent records first. ' This should ensure that the children are also locked! [/COLOR]ON ERROR GOTO parent_lock_failure p[5].change_begin() p[6].change_begin() ON ERROR GOTO 0 [COLOR=blue]'IF lock was successful, begin delete routine [/COLOR]i = 1 WHILE (i <= 6) [COLOR=blue]' Loop through each table [/COLOR] WHILE (numrecs[i] > 0)[COLOR=blue] ' For each table delete each record in the query [/COLOR] IF (tbl[i].mode_get() <> "CHANGE") tbl[i].change_begin() END IF ON ERROR GOTO child_lock_failure tbl[i].delete() ON ERROR GOTO 0 tbl[i].change_end(.t.) numrecs[i] = numrecs[i] - 1 END WHILE [COLOR=blue]'cLose table, drop query, pack table[/COLOR] tbl[i].close() qry[i].drop() ON ERROR GOTO skip_pack tbl[i].pack() skip_pack: i = i + 1 END WHILE [COLOR=#0000ff]' Could not obtain a lock to both of the parent tables.[/COLOR] parent_lock_failure: [COLOR=blue] ' Close all open tables [/COLOR] tbl[1].close() tbl[2].close() tbl[3].close() tbl[4].close() tbl[5].close() tbl[6].close() [COLOR=blue] ' Drop all queries[/COLOR] qry[1].drop() qry[2].drop() qry[3].drop() qry[4].drop() qry[5].drop() qry[6].drop() END' RESUME NEXT child_lock_failure: tbl[i].change_end(.f.) WHILE (i <= 6) tbl[i].close() qry[i].drop() i = i + 1 END WHILE END' RESUME NEXT
This script works by locking a single record in each parent table.
The parent tables are related (not a set) to eachother. They both have a field that is used to correlate records between them.
The thought behind this is that if I am able to lock both parent records at once then all of the related children should also be locked. Is this assumption correct?
I look forward to hearing your thoughts...
Comment