Databases
StackOverflow2013
Postsdbo
POTracking changes in a SQL server 2005 database
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTracking changes in a SQL server 2005 database
    primarykey
    data
    text
    <p>I have been tasked with developing a solution that tracks changes to a database.</p> <p>For updates I need to capture:</p> <ul> <li>date of update</li> <li>old value</li> <li>new value</li> <li>field affected</li> <li>person doing change</li> <li>record id</li> <li>table record is in</li> </ul> <p>For deletes:</p> <ul> <li>date of delete</li> <li>person doing delete</li> <li>The title/description/id of record deleted. The tables I am tracking changes on all have a title or description field. I'd like to capture this before the record is deleted.</li> <li>table record was in</li> </ul> <p>For inserts:</p> <ul> <li>date of insert</li> <li>person doing change</li> <li>record id</li> <li>table record is in</li> </ul> <p>I've thought of a few ways to do this:</p> <ul> <li>I am using stored procedures for any updates/deletes/inserts. I would create a generic "tracking" table. It would have enough fields to capture all the data. I would then add another line in each stored proc to the effect of "Insert record into tracking table". <ul> <li>downside: all updates/deletes/inserts are all jumbled in the same table</li> <li>lots of NULLed fields</li> <li>how do I track batch updates/deletes/inserts? &lt;---- this might not be an issue. I don't really do any thing like this in the application.</li> <li>how do I capture the user making the update. The database just sees one account.</li> <li>edit a lot of existing code to edit.</li> </ul></li> <li>Lastly, I could create a trigger that is called after updates/deletes/inserts. Many of the same downsides as the first solution except: I would have to edit as much code. I am not sure how I would track updates. It doesn't look like there's a way using triggers to see recently updated records.</li> </ul> <p>I am using asp.net, C#, sql server 2005, iis6, windows 2003. I have no budget so sadly I can't buy anything to help me with this.</p> <p>Thanks for your answers!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UST.Rob
    1. USBrian Bolton
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTracking changes in a SQL server 2005 database
      1. USVinko Vrsalovic
      1. VTFavorite
    2. VO
      singulars
      1. POTracking changes in a SQL server 2005 database
      1. USkristof
      1. VTFavorite
    3. VO
      singulars
      1. POTracking changes in a SQL server 2005 database
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload