Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Audit approach for performance tunning Expand / Collapse
Author
Message
Posted Monday, April 21, 2014 4:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 5:06 AM
Points: 190, Visits: 1,514
we do have requirement of tracking insert,update and delete action of tables with tracking userid.
two approach is there.

1) creating single table with below fields:
LogID int
TableName int
Action tinyint (1= Insert,2=Update,3=Delete)
RecordID int
UserID int
CreatedDate DateTime(Default will be current date)

2) Add below fields in all the tables:
modifieddate datetime
modifiedUser int
transactionType Char(1) --this will have action like 'I','D' or 'U' for insert update and delete

which approach will be good for performance ?

Post #1563424
Posted Monday, April 21, 2014 5:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 6,841, Visits: 13,361
The second approach would require to replace the DELETE functionality with a solution that would add a DELETE flag. Otherwise you wouldn't have a row to add the modified* information

Maybe this link can help you to decide what solution would be the best for your scenario.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1563438
Posted Monday, April 21, 2014 7:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 40,194, Visits: 36,599
It's not about performance, it's about what meets your requirements.

Option 2 will only show the last user who changed the row and won't show what they changed. If you need to see all the users who modified the row, then option 2 won't suffice.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1563475
Posted Thursday, May 8, 2014 1:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 3:14 AM
Points: 3, Visits: 8
GilaMonster (4/21/2014)
It's not about performance, it's about what meets your requirements.

Option 2 will only show the last user who changed the row and won't show what they changed. If you need to see all the users who modified the row, then option 2 won't suffice.


Is there any online resource that I can read on how to audit/track who change what value in a record? My audit requirement:
if some one changed a value / values in a record, I need to know who edit the record and which attribute(s) value has been changed (if possible, i need to know what was the original value)..

Thanks!
Post #1568789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse