Cascading deletes using triggers or stored procedure?

10 posts, 0 answers
  1. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 10 Jul 2011 Link to this post

    I am using OpenAccess on top of MySQL, and our tables are MYISAM format. So there is no support for database cascading deletes like there would be if we were using InnoDB. Right now we implement delete logic in code, and manually delete dependent records but that is a bit slow when it comes to doing it in the ORM due to the need to load all the entities when you delete them. So I would like to implement the equivalent of Cascading Deletes in either a stored procedure to delete a record (and delete all related records), or through a trigger in the database.

    If I implement that, how do I then inform OpenAccess to perform a cascading delete on any objects currently in scope in my context? Or do I simply flush the context so it will get everything from the database again after I issue the call that will end up doing the delete in the database?

    I would like to convert over to something that supports this natively, like InnoDB, but I cannot do that just yet so I need to find a solution for the short term.
  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 11 Jul 2011 Link to this post

    Hi Kendall Bennett,

    You could create trigger after delete that will handle this scenario. Once you persist the changes to the database the trigger will be executed.
    Hope that helps.


    Best wishes,
    Damyan Bogoev
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  3. DevCraft banner
  4. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 11 Jul 2011 Link to this post

    Yes, that is exactly what I want to do, but how do I tell Telerik OpenAccess ORM to know to evict all the related entities currently loaded in the identity map when I delete the main entity? I want to use a trigger to emulate a cascading delete, but I need to make sure OpenAccess will clean up any currently loaded entities the same way it would if the database supported native cascading deletes?
  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 12 Jul 2011 Link to this post

    Hi Kendall Bennett,

    You need to use a new OpenAccessContext instance after you delete the objects to achieve that goal. In case of 2nd Level Cache scenario you should evict the instances of all affected types.
    Hope that helps.


    Greetings,
    Damyan Bogoev
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  6. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 12 Jul 2011 Link to this post

    So it sounds like to use cascading deletes with OpenAccess, I need to change to a database that supports it natively so that OpenAccess will then handle it correctly?
  7. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 13 Jul 2011 Link to this post

    Hello Kendall Bennett,

    Could you please try setting the Is Dependent setting of the association’s target property to true? That is, if the referencing instance is deleted then the dependent, referenced instance should be also deleted. In order to achieve that goal you should do the following steps:
    1. Select the association from the visual designer;
    2. Go to the Properties Window grid;
    3. Under the Target Property section set the Is Dependent option to true;
    4. Save the diagram;
    Hope that helps.

    Kind regards,
    Damyan Bogoev
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  8. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 13 Jul 2011 Link to this post

    Yes, that is exactly what I needed!

    Is that documented anywhere in the documentation? I searched for Cascading Delete's but could not find anything that explained what that means, and marking an association as dependent is not something that is obvious to me that it will perform a cascading delete :)
  9. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 14 Jul 2011 Link to this post

    Hello Kendall Bennett,

    You could find this help article useful. It explains the Cascading Delete mechanism in the OpenAccess ORM Classic. The only difference is the way dependent collection is set. You should use the approach suggested in my previous post.
    You could have a look at our Product SDK, which contains a lot of examples for using OpenAccess as well as useful links and contents. All the examples shipped with the SDK have C# and VB.NET versions and are updated. The Product SDK contains an example that demonstrates the Cascading Delete functionality of the product. You could find it under the ASP category.
    Hope that helps. If any other questions arise, do not hesitate to contact us back.

    All the best,
    Damyan Bogoev
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  10. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 14 Jul 2011 Link to this post

    What is the 'Is Managed' property used for? I see it is in both the target and source properties, and if I set 'Is Dependent' to true for the target property in an association (basically the association set in a 1 to many association), it will mark it for cascading deletes, but what does 'Is Managed' mean?

    I assume I need to be careful also about which of the target or source I set this on, because if I set it incorrectly on the source in a 1:Many association, then it would delete things in the wrong direction?
  11. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 15 Jul 2011 Link to this post

    Hello Kendall Bennett,

    1. This means that the collection will be managed by the tracked collection implementation in the product. If you add an element to the collection, the inverse (foreign key) field of the element will be set to the collection and the element is removed from the collection it currently belongs to (if any). You can also set the inverse field in the detail in which case the element will be removed from or added to the master collection automatically.
      2. Yes, you should be careful about setting the Is Dependent option. If it is set on source property the delete operation will be performed in the opposite direction.
    Hope that helps.

    Best wishes,
    Damyan Bogoev
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

Back to Top
DevCraft banner