Data Backup

[coily]

Hi,

Does anyone have advice on the best/most efficient way to do the following: -

Each time a user makes changes to the database i need to log the event. The purpose, being able to roll back/recreate or undo these actions.

I could just create a text log and rely on manual recreation but idealy i would like to design something that allows clients to do this with ease.

Thanks

 

[ThatRickGuy]

Easiest solution would likely be a server side trigger that logs changes, time, and user. Then it's just a matter of building a system that can look at those changes and revert them.

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[coily]

By Server Side trigger do you mean build the function in to the web form itself?

If so, what do you think of using XML for the log as this would give me a structured format with which to work with?

Thanks for the reply,

 

[ThatRickGuy]

Nah, talk to your DBA if you have one. You can set up a trigger, or event in the database that when a change is made it will perform some action. I worked on a system a few years back that had an Oracle back end. Anytime a record was updated/inserted an updated date and updated by field would be populated with the server's time and the username on the connection. I know SQL Server has something similar.

I like XML, but like all tools, it needs to be used to the correct job. XML has a lot of overhead, and all computational power is based on the client (or web server). Also, using an XML file generated by the app depends on the app to function correctly. If someone makes a change, but the app crashes before the XML entry is made, it's lost. Using a database side solution alleviates the processor power requirements of your users, the hard drive space to store an XML datafile, and ensures that the entry will be tracked even if the app dies.

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[coily]

Presumably your suggested method would require code to be written for the SQL server? or is it just a case of configuring built in functions?

My DBA is the host of my testing server i'm not sure how helpful they are but i'll see what they can do.

Many thanks.

 

[ThatRickGuy]

I beleive it would be a matter of writing some stored procedures, setting up some tables, and then setting up the triggers to call the stored procedures when a value is changed. It's not a small solution, but it will provide you with a solid way of tracking all changes. And setting up a system would be a great experience for any DBA.

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.