View help

[wbodger]

I have been tasked with creating a view (actually, multiple views) that have the field AccessDate that holds the last date that view was accessed. Is this possible? The views pull from a number of tables, but since views are just that, views into other tables, is this even possible? If so, how would it be accomplished? Would one of the DMVs have that info for views?

 

[gmmastros]

Unless I am mistaken, it is not possible to track when a view is used (selected from). You can create triggers on views, just like you can for tables, but triggers only fire for insert, updates, and deletes.

It may be possible to use database auditing to keep track of this information, but this is likely to cause a lot of database growth and may not be the best solution.

Does this have to be a view? If you created a stored procedure, then it would be significantly easier. Your stored procedure could be something like...

Create Procedure Whatever
As
  Select .....

  Insert into LogTable(UserName, ProcedureDateTime, ProcedureName)
  Values(suser_sname(), GetDate(), object_name(@@ProcId))

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

I think they will have to be views because of how they will be used, but perhaps the view can call the stored procedure. They views will be accessed by biostatisticians and the data likely pulled out and put into SAS (this last part is kind of a guess on my part as I am new to this job).

Thanks!
WB

 

[gmmastros]

Views cannot call stored procedures. Sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Bummer. I will see if I can come up with a way to use a stored procedure. But, how would that stored procedure show the last date it was accessed? Does that somehow pull something from a DMV?

wb

 

[gmmastros]

No. No DMV's.

Stored procedures can have multiple statements, so you would write to a log file each time the data was accessed and also use that same log file for the report.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

So, after reading the request more closely I see that they are merely requesting the date that 'they' accessed the view. However, I am still interested in the path I was trying to go down, so thank you for your help!

wb