How to delete multiple rows at once

[askoog66]

I could use some advice on best practice of deleting multiple rows from multiple tables at once from a delete button on a grid.

My scenario is a contact table with id# and name that is related to several subtables containing additional info such as telephone #, address, etc.

Therefore, a common scenario would be attempting to delete all of the following at once based on primary key id# of 1776:

Contacts Table:
(one row)
1776 - George Washington

Address SubTable:
(2 rows)
1776 - 1300 Pennsylvania Ave, Washington, D.C. 01010
1776 - 2000 Cherry Tree Drive, Valley Forge, PA 23141

Telephone SubTable:
1776 - 800-123-1234
1776 - 800-321-4321

What I have tried is views - do not work with my backend of Postgres because they are read only.

When the delete Javascript button is placed on the grid for the Contacts toplevel of 1776 - George Washington, an obvious database error of not allowing delete because of related rows in the other tables.

Thanks for the help.

[TheSmitchell]

Re: How to delete multiple rows at once

Couldn't you setup your database to delete the child records for you when the primary record is removed using foreign keys? Does PostgreSQL have Foreign Key constraints?

[PhilM1965]

Re: How to delete multiple rows at once

Hi Sarah,

Have you set up relationships and set the options to cascade the delete? I am not familiar with PostGreSQL, but that is what I would do in MSSQL.

Hopefully that will point you in the right direction at least.

Regards,

Phil

Cascade.jpg

[PhilM1965]

Re: How to delete multiple rows at once

Of course that supposes you wish to delete the primary record too.

If not, then perhaps an AJAX call back with

DELETE FROM PHONES WHERE FKID = MTPK
DELETE FROM ADDRESS WHERE FKID = MTPK

FKID = Foreign key ID and MTPK = Master Table Primary Key

Just some thoughts

Phil

[GGandhi]

Re: How to delete multiple rows at once

hello andrew
i use mysql, so no expert on postgresql.
but the documentation says
you could have two types of foreign key constraints:
1> REFERENCES ... ON DELETE RESTRICT
2> REFERENCES ... ON DELETE CASCADE

so you should be able to delete on deletion of the primary table the foreign key will do what you want to do.

however you could also leave the default and trap when the user wants to delete a primary record that has child record, you could prompt a warning and if they insist then you can delete the primary record and the child records using xbasic. better yet add a field and record that field and keep the records out of view but not deleting them as a safety measure.

the primary key can be obtained from the e. object
the sql delete statement can be constructed using that primary key.

[TheSmitchell]

Re: How to delete multiple rows at once

Second thought is to write your own set of DELETE and execute it in the afterDeleteRecord event. e.dataSubmitted and e.oldDataSubmitted are both available there. Though, personally, I think using foreign key constraints are a better solution.

[askoog66]

Re: How to delete multiple rows at once

Thank you for all of the well thought out answers. I consistently fall in the rut of trying to do everything in Alpha rather than off loading some of the work to the Postgres backend.

I think I am leaning towards Phil's suggestion as I simplified the question more than reality. Although never having programmed an ajax callback, I think this may work better because:

The Contact ID# is potentially contained in many other tables such as Timesheets, TakeOff, WorkOrders, Maintenance, etc.
The contact may also just simply show up in the Contact table if it is just a person we need to track phone, address, etc.
But also, the contact may show up in all of the other tables if it ended up being a customer/job/project.

Therefore, I think I can conjure some better logic by checking to see if the Contact ID# shows up in those other tables, and if not, going ahead and deleting the Contact in the contact tables as well as the closely related address, phone tables.
Otherwise, if the Contact# does exist in the Timesheets, TakeOffs, etc, not allow the contact to be deleted in any table.

I see it working in my head, now if I can muster the neurological power to make it work. Cascading deletes give me the major willies though too. Seems like an easy way to miss some logic and wipe out a lot of data if I don't get things designed correctly.

Many thanks for the help....now on to my first Ajax callback

Andy

[Progytech]

Re: How to delete multiple rows at once

Hi Andrew,

All previous recommendations are pretty good and will work based on your particular need. In your case since the ContactID may be in many other related tables which should prevent the Parent ContactID record from being deleted, it is best to manually deal with this kind of deletion since the CASCADE option of the database will not do it for you.

I use MS-SQL, so for this cases I have a Stored Procedure which will return the COUNT(*) of records in each related table then if this is greater than 0, the deletion of Parent record is not allowed.

Here is a sample code of one of these Stored Procedures:

-- =============================================
-- Author:		<Edhy Rijo>
-- Create date: <7/18/2013>
-- spRelatedRecordsCountForPK_Providers
-- Description:	<Return the number of related records found for this Provider>
-- =============================================
ALTER PROCEDURE [dbo].[spRelatedRecordsCountForPK_Providers]

@PK_Providers AS INT

AS

SELECT
	COALESCE((SELECT COUNT(*) FROM ProvidersCalendar pc WHERE pc.FK_Providers = @PK_Providers), 0) +
	COALESCE((SELECT COUNT(*) FROM Students s WHERE s.FK_Providers = @PK_Providers OR s.FK_Providers2 = @PK_Providers), 0) +
	COALESCE((SELECT COUNT(*) FROM StudentsProvidersLink spl WHERE spl.FK_Providers = @PK_Providers), 0) +
	COALESCE((SELECT COUNT(*) FROM Notes n WHERE n.FK_Providers = @PK_Providers), 0) AS RelatedRecordsCount

Basically I am just counting the records in each related table using the Foreign Key value in PK_Providers. The COALESCE() function will simply return 0 if the result of the COUNT(*) is NULL.

Then in the grid canDeleteRecord() function I have the following code:

function CanDeleteRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as P)
with PageVariables

Result.Cancel = .f.
Result.ErrorHTML = ""

'-- Only allow delete of records if the Provider does not have any related record.
dim conn as SQL::Connection
dim sqlStmt as c
dim rs as SQL::ResultSet	
dim args as sql::Arguments
args.add("PK_Providers", convert_type(DataSubmitted.PK_Providers, "N"))
sqlSelect = "EXEC spRelatedRecordsCountForPK_Providers :PK_Providers"
if conn.Open("::Name::BCI") then
	if conn.execute(sqlSelect, Args) then
		rs = conn.ResultSet
		if rs.DataIsNull(1) = .f.
			dim RelatedRecordsCount as N = convert_type(rs.data("RelatedRecordsCount"), "N")

			'-- If there are related records for this Provider, then abort the deletion process.
			if RelatedRecordsCount > 0 then
				Result.Cancel = .T.
				Result.ErrorHTML = "This provider cannot be deleted.  There are other related records using this Provider."
			end if	
		end if
	end if
	
	'-- There are some memory leaks so be sure the connection is closed.
	conn.close() 
end if

end with
end function

Hope this help.