Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    5

    Unanswered: Trigger vs Application Procedure

    When handling delete operations in an application, are there any recommendations/standards regarding performing the delete in associated tables using a trigger vs processing it in your application code?

    For example, if I have an address table and the user deletes the address, is it better to clean up the associated tables that refer to the address (through a foreign key) using a trigger or in the application code itself?

    The application that I'm working with uses MySQL 5.5 if it makes any difference.


    Cheers, Kris

  2. #2
    Join Date
    Feb 2011
    Posts
    29
    Can only give you what I do which is..

    If it suits I use cascade on the relation which takes care of it.

    If it doesn't suit, I use an on_after_delete method in my model to clean up the orphaned record.

  3. #3
    Join Date
    Mar 2011
    Posts
    5
    Interesting, I had not heard about cascading updates/deletes before. If I understand them correctly, I have a question: If I set an ON UPDATE CASCADE relationship and the entity that the foreign key refers to is deleted, what happens? Does the value for the foreign key automatically get set to NULL?

    For example, if I have the following and an address is deleted, will the customer-->address_id for any rows that refer to that address automatically be set to NULL?

    Customer Table
    address_id (foreign key to address-->id)
    on update cascade

    Addresses Table
    id
    country
    city

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krisdotca View Post
    If I set an ON UPDATE CASCADE relationship and the entity that the foreign key refers to is deleted, what happens?
    nothing -- you need to set ON DELETE CASCADE instead

    ON UPDATE CASCADE means if you change the value of a PK, the related FKs will also be changed

    Quote Originally Posted by krisdotca View Post
    For example, if I have the following and an address is deleted, will the customer-->address_id for any rows that refer to that address automatically be set to NULL?
    no, if you want them set to null then you have to use ON DELETE SET NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •