Post reply

Capture Foreignkey Constraint via Trace?

  • October 16, 2018 at 8:15 am

    #379644

    I was recently working on The Case of the Missing Row and after resolution, I came up with this question: Is there a way to capture foreignkey constraints via a SQL Trace?

    While troubleshooting my issue, I discovered that when a stored procedure deleted a record in one table, the foreignkey also caused a record in a different table to also be deleted. It took me a little longer to find this because first I had to find the procedure, then use print statements to narrow it down to where the issue was happening and finally review the table design to see this.

    I know triggers can be captured in a SQL Trace, however, my issue was the foreignkey. I'm wondering if I could have caught this deletion via a SQL Trace or by using some other method in SQL. Any thoughts?

  • October 16, 2018 at 2:20 pm

    #2009959

    SELECT
          f.name                                                     ForeignKey
       ,  OBJECT_NAME(f.parent_object_id)                            TableName 
       ,  COL_NAME(fc.parent_object_id, fc.parent_column_id)         ConstraintColumn
       ,  OBJECT_NAME (f.referenced_object_id)                       ReferencedObject 
       ,  COL_NAME(fc.referenced_object_id, fc.referenced_column_id) ReferencedColumn
       ,  delete_referential_action_desc                             DeleteAction 
       ,  update_referential_action_desc                             UpdateAction
    FROM  sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
       ON f.object_id = fc.constraint_object_id  
    WHERE
        ( f.delete_referential_action_desc = 'CASCADE'
       OR f.update_referential_action_desc = 'CASCADE' )
      AND f.is_disabled = 0 ;

  • October 16, 2018 at 3:58 pm

    #2009966

    Thank you. I understand this query gives me a list of which tables have foreignkeys on them and what the key is so I can be proactive in identifying and preventing these "accidental deletes" in the future. I was hoping to be able to find some kind of tool that I could have used while troubleshooting the issue of how did this get deleted. Nothing appears to show in a SQL Trace when this happens. Would there be something in a log file somewhere?

  • October 16, 2018 at 4:34 pm

    #2009970

    What version of SQL server are you using?

  • October 16, 2018 at 4:42 pm

    #2009971

    You could create an after delete trigger and insert deleted into a delete history table. You could add a column for SUser_Name() to track who made the delete if they aren't making changes in an application. I they are using an application you could add that to the procedure used to delete making sure you log the deletes in the foreign key table..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply