This topic is locked
[SOLVED]

 Delete Child Table Entries

1/19/2018 1:32:17 PM
ASPRunner.NET General questions
I
i.NoLim author

Hello all!
I have two tables: "Applicant"(Master) and "Charges"(Child). They are link via Applicant_ID. When I delete a row in the master table I would like all entries in the child table linked to that specific row to be deleted. I have the following code in the "Before record deleted" event but this deletes all charges instead of just the ones for this session.

DB.Query("DELETE CHARGES FROM CHARGES C, APPLICANT A WHERE C.APPLICANT_ID = A.APPLICANT_ID");


I've tried other solutions with no success:



//attempt #1

DB.Query("DELETE C FROM CHARGES C JOIN APPLICANT A ON C.APPLICANT_ID = A.APPLICANT_ID");
//attempt #2

DB.Query("DELETE CHARGES FROM CHARGES INNER JOIN APPLICANT ON CHARGES.APPLICANT_ID = APPLICANT.APPLICANT_ID");
//attempt #3

DB.Query("DELETE C FROM CHARGES C WHERE C.APPLICANT_ID = (SELECT A.APPLICANT_ID FROM APPLICANT A)");
//attempt #4

DB.Query("DELETE CHARGES FROM CHARGES C WHERE C.APPLICANT_ID = " + XSession.Session["APPLICANT_ID"]);
Pete K 1/22/2018

What I do is to set this up at the database level, which is foolproof. I use SQL Server. If you have your table relationships set up, you can specify what happens when the parent record is deleted or updated. For deletes, I choose "Cascade", which causes the dependent records in the child table to also be deleted at the same time the parent record gets deleted. The great thing about taking care of things like this at the DB level is that no matter where the delete occurs -- even in other apps/DB tools -- your referential integrity is always maintained.

jadachDevClub member 1/22/2018



What I do is to set this up at the database level, which is foolproof. I use SQL Server. If you have your table relationships set up, you can specify what happens when the parent record is deleted or updated. For deletes, I choose "Cascade", which causes the dependent records in the child table to also be deleted at the same time the parent record gets deleted. The great thing about taking care of things like this at the DB level is that no matter where the delete occurs -- even in other apps/DB tools -- your referential integrity is always maintained.


This is exactly how I do it as well.

I
i.NoLim author 1/22/2018

Thank you very much for your replies. I have 2 questions,
[list=2]
[]The two tables I posted are part of a bigger project where another table is involved, "Family." Family is the master table for Applicant (linked through FamilyID), and Applicant is the master table for Charges(Linked through ApplicantID). If I set the foreign key in the Applicant table as FamilyID and the foreign key in the Charges table as ApplicantID and add ON DELETE CASCADE on both tables; deleting a Family entry would also delete every Applicant that's a member of that family as well as all their charges, correct?
[
]I already have "Before record updated" and "After record updated" events that I thought only ran for the specific entry being updated. After running the code above it seems like its updating the whole database, is that the case? See sample code below.

[/list]



//After record update event
DB.Query("UPDATE A SET A.SUB = C.CHARGESUM FROM APPLICANT A INNER JOIN( SELECT APPLICANTID, SUM(PAID) CHARGESUM FROM CHARGES C GROUP BY APPLICANTID)C ON A.APPLICANTID = C.APPLICANTID");
DB.Query("UPDATE F set F.TOTAL = A.SUBSUM FROM FAMILY F INNER JOIN(SELECT FAMILYID, SUM(SUBSIDY) SUBSUM FROM APPLICANT A GROUP BY FAMILYID) A ON F.FAMILYID = A.FAMILYID ");
Pete K 1/22/2018



Thank you very much for your replies. I have 2 questions,

  • The two tables I posted are part of a bigger project where another table is involved, "Family." Family is the master table for Applicant (linked through FamilyID), and Applicant is the master table for Charges(Linked through ApplicantID). If I set the foreign key in the Applicant table as FamilyID and the foreign key in the Charges table as ApplicantID and add ON DELETE CASCADE on both tables; deleting a Family entry would also delete every Applicant that's a member of that family as well as all their charges, correct?


No. Each relationship has it's own rules for handling updates and deletes. If you add this rule to the relationship that links [Applicant] to [Charges], it should have no effect on any other relationships. The [Family] table would not be affected at all.

I
i.NoLim author 1/22/2018



No. Each relationship has it's own rules for handling updates and deletes. If you add this rule to the relationship that links [Applicant] to [Charges], it should have no effect on any other relationships. The [Family] table would not be affected at all.



What I meant by it is that if Charges is linked to Applicant and Applicant is linked to Family. If I delete a Family, all the Applicants linked to that family would be deleted automatically and because some Charges are linked to those applicants these would also be deleted. Am I correct in this assumption? I apologize, I've never worked with ON DELETE CASCADE before.
As far as my other question, does ASPRuner automatically know that I'm only trying to update the data for the entries that share the same FAMILYID and APPLICANTID as the entry on the working session?

I
i.NoLim author 1/22/2018

After some testing I figured out that the code above updates the whole DB, that's a problem.
I know there's something like

XSession.Session["APPLICANT_ID"]


If I made a change in the "Charges" table (child table), what's the syntax to also update the "Applicant" table (master table) only for that specific applicant?
This is what I have right now, but it doesn't work.

DB.Query("UPDATE A SET A.SUB = C.CHARGESUM FROM APPLICANT A INNER JOIN( SELECT APPLICANTID, SUM(PAID) CHARGESUM FROM CHARGES C GROUP BY APPLICANTID)C ON A.APPLICANTID = "

+ XSession.Session["APPLICANTID"]);
jadachDevClub member 1/23/2018

Try something like this on before deleted event of Applicant Table:

dynamic tblCHARGES = GlobalVars.dal.Table("dbo.CHARGES");

tblCHARGES.Param["APPLICANT_ID"] = deleted_values["APPLICANT_ID"];

tblCHARGES.Delete();
I
i.NoLim author 1/24/2018

Thank you jadach.
I'm trying to use a similar code to update my Family and Applicant tables when a new Charge is added or updated. I added the following code to the "Before record updated" event but get the error in the picture below.
Edit: Wanted to add a description of what this code is doing when a new charge is added/updated,

  1. It looks at the APLICANT_ID of the charge being added/updated
  2. Searches for this applicant in the "Applicants" table
  3. Adds all "Charges" that have this same "APPLICANT_ID" and updates "Subsidy" in "Applicants" with this value.


Something similar will be done for the "TOTAL" in the "Family" table but we would be adding all "Subsidy," from the "Applicant" table, with the same "FAMILY_ID."

dynamic tblCharges = GlobalVars.dal.Table("dbo.CHARGES");

tblCharges.Param["APPLICANT_ID"] = values["APPLICANT_ID"];
string strSubsidy = "UPDATE A SET A.SUBSIDY = C.CHARGESUM FROM APPLICANT A INNER JOIN( SELECT APPLICANT_ID, SUM(PASS_PAID) CHARGESUM FROM CHARGES C GROUP BY APPLICANT_ID)C ON A.APPLICANT_ID = " + keys["APPLICANT_ID"].ToString();

CommonFunctions.db_exec(strSubsidy, null);


admin 1/24/2018

Looks like an issue with SQL Query you trying to execute. Instead of executing it print this query on the web page to see what exactly went wrong:

https://xlinesoft.com/asprunnernet/docs/debugging_tips.htm

I
i.NoLim author 1/24/2018

Thanks to everyone for your replies. I finally got it, further testing needed. At first I wrote the code in the "Before record updated" event but it was adding the values before the item was added/updated. So I thought, "well... I can just copy the code to the 'After record updated' event," no luck. I had to change data[""].ToString() to values[""].ToString(). Side question, whats the difference between "values" vs. "oldvalues?" Is "oldvalues" the value of the field before the update?
Thanks again.

string strSubsidy = "UPDATE A SET A.SUBSIDY = (SELECT SUM(PASS_PAID) CHARGESUM FROM CHARGES C WHERE C.APPLICANT_ID ='" + data["APPLICANT_ID"].ToString() + "')FROM APPLICANT A, CHARGES C WHERE A.APPLICANT_ID = '" + data["APPLICANT_ID"].ToString() + "'";

CommonFunctions.db_exec(strSubsidy, null);