The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Secondary table not deleting
Posted: 23 Feb 07 7:34 AM
|
fiogf49gjkf0d I have set up a table via Database Manager that is associated with a parent on a 1 to many basis. This works fine, and can be accessed via Query Builder and Architect with no problem.
However, in testing my modification and deleting the parent record, I find that the secondary table is left with the associated records, now as orhpans.
Obviously if I had set up a manual join I would be checking that I had set the correct cascade option, but this join is set up via Database Manager and I cannot see either where I can have any input into the cascade option, or where I can view how SLX has set it up.
Any ideas welcome! |
|
|
|
Re: Secondary table not deleting
Posted: 24 Feb 07 12:31 PM
|
fiogf49gjkf0d If you are deleting the parent record(s) via code (Ex: Delete * from X where ID = 'SomeID') then it does NOT cascade delete. You must use the "cascadedelete" call:
Application.Basicfunctions.CascadeDelete
--
rjl |
|
|
|
Re: Secondary table not deleting
Posted: 24 Feb 07 12:44 PM
|
fiogf49gjkf0d Thanks Bob - will store that away for reference - but the deletion here was simply via the right-click delete from a datagrid. |
|
|
|
Re: Secondary table not deleting
Posted: 01 Mar 07 9:29 AM
|
fiogf49gjkf0d Check the join between the tables and make sure it says Cascade delete maybe the join is messed up in the SLX tables |
|
|
|
Re: Secondary table not deleting
Posted: 01 Mar 07 9:36 AM
|
fiogf49gjkf0d I think I must be short of a brain cell or two here!
Where can I check the join? As I said, the secondary file was set up via Database Manager, and I cannot find a way of looking at the joins it sets up. If I try to add another join via Global joins, it just tells me that it is a duplicate! (Or are you suggesting looking directly at the SQL database?) |
|
|
|
Re: Secondary table not deleting
Posted: 01 Mar 07 9:38 AM
|
fiogf49gjkf0d in the Architect Manage Global Joins.. just check it to make sure it is setup correctly.. no need to build another if it is setup coirrectly.. was just a place to check for you |
|
|
|
Re: Secondary table not deleting
Posted: 01 Mar 07 11:20 PM
|
fiogf49gjkf0d Originally posted by Colin Bernard
I think I must be short of a brain cell or two here!
Where can I check the join? As I said, the secondary file was set up via Database Manager, and I cannot find a way of looking at the joins it sets up. If I try to add another join via Global joins, it just tells me that it is a duplicate! (Or are you suggesting looking directly at the SQL database?) |
|
ALL SalesLogix (Global Joins) are managed via the Join manager... (Global Joins). It's got nothing to do w/SQL server itself
Basic SalesLogix - ALL Meta data is maintained by SalesLogix.. not the underlying DB engine. It's always been this way (10 years) and will always be this way as long as SalesLogix remains database agnostic.
--
rjl |
|
|
| |
|
Re: Secondary table not deleting
Posted: 02 Mar 07 8:01 PM
|
fiogf49gjkf0d I take it you ran the SlxProfiler against it as well?
Humm... Starting to sound like a bug...
I'll give it a test tomorrow.. just got back in from a trip to Canada.. (and my bags did not...)
--
rjl
|
|
|
|
Re: Secondary table not deleting
Posted: 03 Mar 07 3:51 AM
|
fiogf49gjkf0d Glad it's not just Europe where bags decide to take their own vacation!
Yep - Profiler was not that much help other than confirming SLX was treating the 2 deletion routes differently.
Deleting from the survey grid in the contact main view gave a single DELETE statement, then the SELECT to repopulate the grid.
Deleting the contact gave a couple of SELECTS against Survey and SurveyQ to get the IDs for the rows to be deleted, then a DELETE for each SurveyQ followed by a DELETE for Survey.
There were no error messages.
(PS - Appreciate you taking the time here Bob!) |
|
|
|
Re: Secondary table not deleting
Posted: 06 Mar 07 7:44 AM
|
fiogf49gjkf0d Here's a "fix"..
Capture the delete "event" and get the PK for the row/record being deleted.
Issue a CascadeDelete (Applications.basicfunctions...) against the PK..
Oh yes, the bag did arrive safely by personal limo to my home.. a day later.. it had to "sleep" at the airport alone 
--
rjl |
|
|
|
Re: Secondary table not deleting
Posted: 06 Mar 07 9:17 AM
|
fiogf49gjkf0d Thanks Bob, I'll try that when I get a minute. My workround at the moment is a script that looks for orphaned rows and deletes them.
Once again much appreciated.
(Hope the bag behaved itself when it was sleeping away - don't want the patter of tiny purses!) |
|
|
|
Re: Secondary table not deleting
Posted: 07 Mar 07 11:52 AM
|
fiogf49gjkf0d The current datagrid does NOT call CascadeDelete or some internal variation. That's what RJ said eventually, but it's one of those things that everyone should be aware of up front. I didn't figure it out until I read up on CascadeDelete in 6.2 developer's reference back when we were on 6.1. It affects every version since 6.0 I believe, v7 included. Legacy grids never had this problem so it's a wonder why the new datagrid control doesn't call cascadedelete under the hood.
If you're on v7 I'd suggest making a Integrity Checker entry that scans your custom tables. Scanning and deleting could be done automatically but you introduce a performance penalty if you call it every datagrid delete. |
|
|
|
Re: Secondary table not deleting
Posted: 07 Mar 07 12:28 PM
|
fiogf49gjkf0d Thanks Jeremy - that explains all.
We are on v6 at the moment so will live with the script. Deletes should be few and far between because of the nature of the data and only required after an 'oops!'. |
|
|
|