none
Find cascading deletes that effect a specific table RRS feed

  • Question

  • I'm having problems with my SQL Server based application (C#/Winforms). On more than one occasion, a user has complained that a certain key record has deleted itself. After some investigation, it seems they are right - there are clues that the record once existed (one other table has referneces to the missing record). Also, it seems that this was not accidental deletion by a user (I have other functionality which would catch this out).

    I'm now trying to work out if the record is being deleted at the SQL level. If somehow, by deleting a record from a related table, it had deleted a record from another table which it should not.

    So, I'm looking for some way to investigate this. An easy way to point at a table, and query the system to tell me what events/triggers (like a cascading delete) could delete a record in this specific table?

    Monday, March 11, 2013 1:57 AM

Answers

  • Check this query

    SELECT C.*
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    WHERE DELETE_RULE = 'CASCADE' 
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 11, 2013 2:08 AM
    Moderator
  • in sql server

    right click on the tablename -> script table as -> create to -> New query editor window.

    now you can see wether the table has any cascade delete or not


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by fweeee Monday, March 11, 2013 10:53 AM
    Monday, March 11, 2013 2:31 AM

All replies

  • Check this query

    SELECT C.*
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    WHERE DELETE_RULE = 'CASCADE' 
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, March 11, 2013 2:08 AM
    Moderator
  • in sql server

    right click on the tablename -> script table as -> create to -> New query editor window.

    now you can see wether the table has any cascade delete or not


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by fweeee Monday, March 11, 2013 10:53 AM
    Monday, March 11, 2013 2:31 AM
  • in sql server

    right click on the tablename -> script table as -> create to -> New query editor window.

    now you can see wether the table has any cascade delete or not


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Thanks. This did the trick I think. I found a few which had "ON DELETE SET NULL" in them, but none that delete the record.

    I guess I'll keep looking within the app.

    Monday, March 11, 2013 10:52 AM