Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-01-15 : 07:39:01
|
| Mesrop Simonian writes "I know there is no such thing as trigger on select, however, what I want is to update a viewcount field each time the record is selected. Is it a trigger? a rule? or is it simply done in the stored procedure itself? if it's the last case, how does one loop thru all records returned by a select query and update each record's viewcount?any input will be greatly appreciated." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-15 : 07:44:02
|
| You can have the procedure UPDATE the column's value:CREATE PROCEDURE GetData ASSET NOCOUNT ONSELECT * FROM myTable WHERE col1='A'UPDATE myTable SET ViewCount=ViewCount+1 WHERE col1='A'Note that the WHERE clauses for the SELECT and UPDATE must be identical for this to work. If you are allowing ad-hoc or dynamic SQL queries (bad), you'll have a hell of a time making this work. You'd have to parse out the WHERE clause, and any JOIN clauses. AND it won't work with GROUP BY or HAVING clauses.Can you explain the reason for this feature? It doesn't seem particularly useful to me, and you will experience HUGE performance losses if this table gets a lot of traffic. |
 |
|
|
|
|
|
Trigger on Select