Counting number of selects made

COUNTING NUMBER OF SELECTS MADE

table mytable {
id, data, hits
}

users view data from the table:

SELECT data FROM mytable WHERE id=1 --for example
SELECT data FROM mytable WHERE id=20 --for example
...

How do increment the hits column without replacing the above with the below?

update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=1
update mytable SET hits=hits+1 WHERE id=1;SELECT data FROM mytable WHERE id=20
...

I believe triggers can't be used as they only trigger on update/delete events.

I'm using sql server 2000 (latest patches) with asp

Thanks

AlexThere is no reliable way to do this. One option is to use the profiler to
track the SELECT statements and dump them into a file. This can be exported
in a SQL Server table later on.

--
- Anith
( Please reply to newsgroups only )|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message news:<M4COa.45067$3o3.3029330@.bgtnsc05-news.ops.worldnet.att.net>...
> There is no reliable way to do this. One option is to use the profiler to
> track the SELECT statements and dump them into a file. This can be exported
> in a SQL Server table later on.

I use stored procedures for everything. This is one reason why. You
can add auditting information whenever you like.

In a related story, I also have an USERID parameter on every stored
procedure for future use. (Then you can log who queried what).|||A Seel (alex_seel10@.hotmail.com) writes:
> COUNTING NUMBER OF SELECTS MADE
> table mytable {
> id, data, hits
> }
>
> users view data from the table:
>
> SELECT data FROM mytable WHERE id=1 --for example
> SELECT data FROM mytable WHERE id=20 --for example
> ...
>
> How do increment the hits column without replacing the above with the
> below?

As Anith said, there is no supported way to this, other than to run a
Profiler trace.

Lumigent has a tool called Entegra, which they say will include a module
that audits who views which data. Their web site says that this module
is planned for release Q2, 2003, so one would expect it to appear soon.
See www.lumigent.com for more information.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp