locked
What is the best approach to track deleted records RRS feed

  • Question

  • Dear all,

    We have build a CMS platform which is based on SQL server 2012 tables structure hosted in Azure.
    We have build on top of this some REST API method in order to access data from any type of client application.

    The issues we need to solved now is what his the best way to track deleted records in order that client application gets informed through web service about deleted data from our CMS.

    We were thinking of 2 path actually :

    - having a kind of Ghost table for each of our real table where deleted records will be inserted into ( physical delete ). This would mean adding as many Ghost tables as we have production tables

    - Adding a IsDeleted flag to each of our table which will be set to true when a record is deleted from our CMS ( logical delete ). This would means adding an IsDelete field to each of our tables, create and update all our store procedure and web services in order to taken in account that new filter criteria to fetch our records. Quite huge job

    Will there be any other approach ?

    We are looking the best solution with minimum impact on our current solution

    reagards


    Your knowledge is enhanced by that of others.

    • Moved by Kalman Toth Tuesday, June 17, 2014 11:28 AM Not database design
    Monday, June 16, 2014 5:15 PM

Answers

  • I would seriously rethink locally caching data and what you are actually gaining from doing that.

    But in this case, I would use a trigger to LOG the deleted primary key.  Then have a separate process which intermittently sends a message to the client the key was deleted (once a minute or something).

    Tuesday, June 17, 2014 6:47 PM

All replies

  • Triggers will work well with a minimum impact on your current solution. 

    If you're running SQL Server 2012 in an Azure VM, then you can use the built-in Change Tracking feature, but if you're using Windows Azure SQL Database, that feature is not available, and you'll need to provision triggers and tracking tables.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Sofiya Li Tuesday, June 17, 2014 7:31 AM
    Monday, June 16, 2014 5:34 PM
  • The best approach is to have that in the requirements up front and not have to add it on later.

    The question would be what do you need to do with the deleted records and how long do you need to keep them?

    The minimal impact solution is to copy/move them to a "deleted" table and then keep all your code the same.  However, if you need to do any reporting on deleted and current items, then you have a ton of work.

    I prefer adding an IsDeleted datetime field.  Then you can do point-in-time reporting.  I realize this is a big change, but it gives the best flexibility.

    • Proposed as answer by Sofiya Li Tuesday, June 17, 2014 7:30 AM
    Monday, June 16, 2014 5:36 PM
  • Hello,

    @Tom, based on your question

    "The question would be what do you need to do with the deleted records and how long do you need to keep them?"

    When records is deleted, then I simply want to delete them and informed any client application about deleted items in order to get data in Sync. I will not have any reporting on deleted data !

    The only reason of tracking delete tables items, is simply to informed client application through web service sync about the data to be ignored. Client application have a caching database records for performance reason and is is require to not used data from that local storage which has been reported as deleted by the SQL server on Aure.

    Does this make sense ?

    regards


    Your knowledge is enhanced by that of others.

    Monday, June 16, 2014 6:02 PM
  • As David and Tom have nicely explained the required solutions.

    Along with that you may need to design tables structure to process such deleted records handling by either of the approach mentioned by them. Triggers seems to be a good option here to try.

    Also in the code where you give option for deletion, you may modify codes to store that data first rather than going for Triggers!!Is Deleted Flag too seems to be good one.


    Santosh Singh

    • Proposed as answer by Sofiya Li Tuesday, June 17, 2014 7:30 AM
    Monday, June 16, 2014 6:44 PM
  • In addition to above, you can take a look into this proficient tracking application(http://www.sqlserverchangetracking.com/) that will help you to audit all the changes made in SQL server. It instantly alerts of all critical changes made in SQL server by sending customized email notification.

    Tuesday, June 17, 2014 11:11 AM
  • I would seriously rethink locally caching data and what you are actually gaining from doing that.

    But in this case, I would use a trigger to LOG the deleted primary key.  Then have a separate process which intermittently sends a message to the client the key was deleted (once a minute or something).

    Tuesday, June 17, 2014 6:47 PM