Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
josephj1989
Starting Member
7 Posts |
 Posted - 2010-05-15 : 23:59:48
|
| Hi I am relatively new to SQL Server and I am designing a small database .Some tables need to store information like date time of last update and user who last updated each record.This is a typical audit requirement.I can add extra fields to each table for this.But I am wondering whether I can use any standard SQL Server Audit/Change Tracking features to satisfy my requirement.Please advice |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
josephj1989
Starting Member
7 Posts |
Posted - 2010-05-16 : 04:34:03
|
| Hi Tarathanks for your reply. Mine is a simple ASP.net c# web application using Linq for data access.I can easily populate the said audit fields from c#(in the linq entities).But I wanted to avoid doing all that if some automatic auditing option is available in sql server (In this case I dont even need to create audit fields in database tables). So I just wanted to confirm that no automatic features are available before I (manually) implement these.thanks |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 05:22:29
|
Implement them using a Database Trigger, then it doesn't matter how the data is updated, the UpdateDate column will always be set.All our tables (well, 99.99% of them!) have Create/Update Date and Create/Update User. We have an additional field for "Edit Version" to prevent someone overwriting a modification by someone else. Probably using a ROWVERSION datatype column would be better. (Retrieve the RowVersion value, along with the other values your application needs, then when the application saves the data it includes that in the WHERE clauseUPDATE USET Col1 = 'Value1', ...FROM MyTable AS UWHERE U.MyID = '123' AND U.MyRowVersion = '456'IF @@ROWCOUNT <> 1 THEN ... update failed, raise error! ... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-05-16 : 07:37:06
|
| we also have similar columns called (datecreated,createdby,datemodified,modifiedby,timestampvalue) in all tables for audit. the date fields will be defaulted to GETDATE() and created/modifiedby to SUSER_SNAME(). In addition, we will have a trigger for update to set datemodified and modifiedby. the timestampvalue is rowversion column which will be retrived before update and then checked before doing save------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
SQL Server Audit