?
Solved

sql-server 2008  trigger for counting row access.

Posted on 2013-05-19
9
Medium Priority
?
344 Views
Last Modified: 2013-05-25
Hi!

I have a table like this! (UserData)
 itemId, Itemname, ItemUrl,,,,,,,,,


then i have an other table (Accesscounter)
Itemid, NumberOfDOwnload, LatestDownload,,,,,

i  need a trigger into sql-server2008 that updates the Accesscounter everytime
a row in Userdata is accessed.  
so when itemid x in Userdata is accessed it needs to write the itemid into accessCounter and increment number of downloads and the datetime for latestdownload.  
Btw. if the item in AccessCounter already exists it needs to update the row not create a new one!

How would this be done with a trigger? now i deal with it in code but a trigger would be a nicer solution.
0
Comment
Question by:jamppi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39179667
With SQL 2008, you can use the MERGE feature that can handle the UPDATE or INSERT, depending on existence of match. Therefore, you can have your trigger aggregate the UserData by ItemId to get COUNT of downloads and MAX download date. You can then use the result as the source table and Accesscounter as the target table in the MERGE statement.

MERGE: http://technet.microsoft.com/en-us/library/bb510625.aspx
CREATE TRIGGER: http://msdn.microsoft.com/en-us/library/ms189799(v=sql.105).aspx

Does this make sense?

Please give it a try and post back any questions as well as some sample data and the expected results.

Kevin
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 39179823
Hi,

Maybe I'm missing something, but isn't the asker asking for every time the row is selected, as well as inserted/updated/deleted?

The modifications can use a trigger - insert/update/delete - but not the select.

If all access was via stored procedures, then adding this logging would be easy. But I'm not sure how to do the selects without a procedure as an interface.

Regards
  David
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39179904
How would this be done with a trigger
As David has indicated you cannot have a TRIGGER on a SELECT.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:jamppi
ID: 39180520
Acperkins.

So i guess i need some other approach to this then.

/j
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39180540
Hi

Yes.

I pointed out one alternative ...

Regards
  David
0
 

Author Comment

by:jamppi
ID: 39180591
dtodd:

So you suggest that i would create a stored procedure, have the query run against the sproc  and have that in turn update the accesscounter?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39181355
Yes, I missed you wanted this on SELECT. My understanding is that a simple query of the items should not update a counter that it was downloaded. What if I simply kept refreshing the application (web page) without ever downloading the file? My initial guess was the application would write back a date or a new record whenever the application downloaded the file. Therefore, I agree with others, but I would also look at your application design and definition of a download if it is triggered on a simple SELECT statement.
0
 

Author Comment

by:jamppi
ID: 39181443
ok.  well this is a part of a REST api.
So when the query returns it actually changes the 'Response.ContentType' to Pdf.
that is why i can be somewhat sure about the download process.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39181465
That makes sense. If you are unable to use the stored procedure idea, then you can see if you can have the web side put a value in database before transferring to view PDF. That way, you can trigger on the INSERT or UPDATE.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question