I am developing a custom module.

When logged in with Admin rights I select a module header and then in the drop down, select Delete. The instance of the module on the page is deleted. When I look in the SQL Server table associated with the module, I see ModuleID entries associated with modules that have been deleted.

On what event do I perform a call to a stored procudure to delete the ModuleID rows where ModuleID = the ID of the module I am deleting?

I am talking about deleting rows in one of my custom modules tables.

Over the years  have seen a lot of problems with placing foreign keys on Core tables. The problem is as usual when you upgrade the DotNetNuke site. The Core will make upgrade scripts that will drop keys so that it can migrate data. Of course we don't know about a foreign key you placed so the upgrade script fails.

My recommendation is to not delete the data. For example the Survey module simply leaves the data for the survey siting in the Survey tables when you delete a survey.

If this left over data really bothers you then create a scheduled process (using the DotNetNuke scheduler) that will clean out the orphaned records