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

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

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

<< 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