Conditional Auditing

Aug 8, 2011 at 8:19 PM
Edited Aug 8, 2011 at 8:20 PM

What would be involved in turning off auditing of a single column in an entity?  Lets say I have an image field (varbinary(MAX)) that I don't want to audit along with the rest of my entity because I want to save space and don't really care about the history of that field.

My concern is of course that the entries into the column varbinary(max) could be quite large and would eat database space for no reason.

Any Ideas or suggestions?

Steve

Aug 9, 2011 at 9:26 PM
Edited Aug 9, 2011 at 10:18 PM

Also,  It would be nice to have a generic runselect on the audit entities.  This would enable me to select all the audit records by one of their foreign keys.

I might want the audit history for all of the students in a class; so that I can audit when a student was removed from a class.  I currently have no built in way to do that, besides selecting all of the audit records from "ClassStudents" and then creating a LINQ where clause.

EXAMPLE;

 

Dim lst = ClassStudents.GetAuditRecords(0,0, Nothing, Nothing)
Dim lstStudentsInClass = from audit in lst.innerList
                         where audit.ClassID = 5
                         order by audit.AuditDate

 

I hope I have made my dreams clear.

Thanks,

Steve

Aug 16, 2011 at 8:43 PM

For now I have just implemented custom parametrized SQL in the various audit classes where I need this functionality.  I may look to move this to stored procedures when I find the time.  I still think it would be nice for the audit classes to behave similarly to the regular business objects/collections.

Does anyone have any comments on this?

Steve

Sep 16, 2011 at 10:33 PM

Still looking for a suggestion on how to turn auditing off for a single large column in a table.  (on for the rest)

I may need to look into customizing the trigger.

Any Ideas?

Steve

Sep 17, 2011 at 9:44 AM

maybe another table  with one-to-one relationship on the columns you do not want auditing.

giannis

Sep 19, 2011 at 2:45 PM

Thanks for the suggestion.  unfortunately, at this point to go back and seperate that column from the table would involve fairly extensive code changes.

Is there a file in the Database Installer which is a generate once file?  I would just script the trigger as I want it and plop it in there if the installer runs it after the create schema?

If I put it in the upgrade script will it get overwritten with the next version of SQL generated?

Steve

Coordinator
Sep 20, 2011 at 1:09 AM

A 1:1 table for large columns that is not audited is the only way you are going to do this right now I think. This is a good idea. We just have no way to toggle individual columns presently.

Sep 20, 2011 at 2:38 PM

I suppose I could extract the column and use a property in the entity to expose the new table's column.  Hopefully, this results in the least amount of code changes in my presentation layer.

 

I look forward to the ability to toggle columns individually :)

 

Steve

Sep 21, 2011 at 2:45 PM

Ok, so it was less effort then I anticipated.  (glad I'm still in development)  I extracted the column to an additional table (1:1 relationship) and created a property that dealt with that in the generate-once file for the original entity.  There was no code changes required to my presentation layer.

Thanks for the suggestion,

Steve