Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 4 of 4 total |
Handling Cascading Deletes? |
Sun, Mar 31 2019 3:04 PM | Permanent Link |
Trinione | Good day,
I have seen discussion in the forum that Cascading Deletes is not currently a built-in feature and a recommended method of handling this is via Triggers. That said, I would like to present the user with relevant information and provide them a prompt alerting them to the exact tables which are preventing the deletion from taking place. I have setup the relevant Custom Error Codes/Messages in the tables and can see the message displayed via the EDBQuery.DeleteError event. However, I am only seeing one of several messages and not from multiple 'child' tables. -------------------------------------------- procedure Tfrm1.edbqry1DeleteError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction); begin ShowMessage('In edbqry1DeleteError(). The EDatabaseError msg is: ' + E.ToString); end; -------------------------------------------- My questions at this point are: 1 - How can I get all of the messages from the various 'child' tables that the condition violates, and not just one? 2 - Is it possible to get the Custom Error Code entered in the table's Constraints | Error tab? 3 - Can the tables causing the violation be accessed in an array? That way one could display a message dialog along the lines of - 'Unable to delete due to record/s existing in Table X, Table P and Table H'. 4 - With a Check Box - [ ] Delete all child records - value of True, can this be sent to the procedure in order to remove all child records of related tables? 5 - I take it that without the built-in Cascading Delete feature, the developer is responsible for ensuring that all child tables are included in all relevant triggers? Regards. ........................................................ PascalNetwork.com pascal - the language we love |
Sun, Mar 31 2019 3:33 PM | Permanent Link |
Trinione | After including the following code (as indicated in a post from April 2010), the Error message warning is no longer displayed.
It does the checking correctly, it just does not show the Custom Error message or any Error message. I have also tested via the EDB Manager. --------------------------------------------------------------- CREATE TRIGGER BeforeDeleteTrigger BEFORE DELETE ON Master BEGIN START TRANSACTION ON TABLES Master, Detail; END CREATE TRIGGER AfterDeleteTrigger AFTER DELETE ON Master BEGIN EXECUTE IMMEDIATE 'DELETE FROM Detail WHERE MasterID='+OLDROW.ID; COMMIT; END CREATE TRIGGER ErrorDeleteTrigger ERROR DELETE ON Master BEGIN ROLLBACK; END ---------------------------------------------------- ........................................................ PascalNetwork.com pascal - the language we love |
Mon, Apr 1 2019 3:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Trinione
Is there anything in your metadata that would allow you to track downwards from the table you want to delete from? If so, whilst it will be a bit long winded, it will be possible to trace the entire stack of things to be deleted and in which order. Start with the current table, query the information in information.constraints eg select * from information.constraints where Type = 'ForeignKey' and TargetTable = 'table I want to delete from' that will give you a list of the tables involved - query each of those and build a list of all the tables involved When you have the full list of tables start with the table you want to delete (lets call it A) and check the foreign keys for the row you want to delete against all the other tables and see what you want to delete. Just in case you have two or more rows in A that have the same foreign key you'll need to check that Its doable but not easy. Brute forcing it within a transactions and rolling back on any exception is probably easier. Combining the two would make for a fairly robust and safe process but again complex. I hate RI and tend to avoid it wherever possible simply because its very hard to recover from errors (I'm an antique and remember the days when you got a computer working and just didn't move it or speak loudly near it) Roy Lambert |
Mon, Apr 1 2019 2:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << How can I get all of the messages from the various 'child' tables that the condition violates, and not just one? >>
EDB will only report a constraint violation as it encounters them. << 2 - Is it possible to get the Custom Error Code entered in the table's Constraints | Error tab? >> Yes, just check to see if an exception is an EDB exception, and then access the relevant properties: if (E is TEDBError) then ShowMessage(IntToStr(TEDBError(E).ErrorCode)); https://www.elevatesoft.com/manual?action=viewcomp&id=edb2&product=rsdelphiwin32&version=10R&comp=EEDBError << 3 - Can the tables causing the violation be accessed in an array? That way one could display a message dialog along the lines of - 'Unable to delete due to record/s existing in Table X, Table P and Table H'. >> No see 1) above. << 5 - I take it that without the built-in Cascading Delete feature, the developer is responsible for ensuring that all child tables are included in all relevant triggers? >> Yes. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Monday, May 6, 2024 at 03:23 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |