Author |
Topic |
kaisdd
Starting Member
17 Posts |
Posted - 2006-04-05 : 10:24:01
|
hi, i think i need help with a sql server trigger.i got 2 tables. users and comments.i like to write a trigger which updates User.CommentCount when comments got deleted.delete statement is something like this:delete from Comment where Comment.UserID = 8my current trigger looks like that:CREATE TRIGGER TRCommentDelete ON dbo.pcComment FOR DELETE ASBEGIN UPDATE pcUser SET UserCommentCount = UserCommentCount - 1 FROM deleted WHERE pcUser.UserID = deleted.CommentAuthorIDENDproblem with this trigger is, that the count only gets reduced by 1 regardless of if a user has more than 1 comment.i need a trigger which updates CommentCount by the number of comments by a user that got deleted.thanks. kai. |
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-05 : 10:27:58
|
Your comment count is derived data. It is calculated from existing data. Except in extreme circumstances, is not good practice to store derived data. Consider creating a view that includes this data, and if performance is critical you may be able to convert it into an indexed view. Triggers are not the best way to implement this particular business logic. |
|
|
kaisdd
Starting Member
17 Posts |
Posted - 2006-04-05 : 10:35:02
|
mhm, okay. but CommentCount is only used to display a status in a user profile. isn´t it a bit to much to create a view for this? |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-05 : 12:48:42
|
Not if you want accuracy. Again, an indexed view would get your pretty good response time too, though it is probably not worth it unless you are dealing with an extremely large amount of data.select count(*) from comments where user_id = @user_id should run very fast, especially if user_id is indexed. |
|
|
kaisdd
Starting Member
17 Posts |
Posted - 2006-04-05 : 15:38:35
|
okay, thanks for your point of view.i tested a little but i´m not sure which is the right way to do the counting.i tried to go with indexed views but i think i need a subquery to get the count and this isn´t allowed in indexed views. e.g. select userid, (select count(*) from comment where commentuserid=userid) as commentcount from usershould i use a normal view with appropriate indexes in the underlying tables? is the performance okay?is there an other way to calculate and store this count?the user table has around 200.000 records. the comment table hast arround 100.000. reads are very often. updates and inserts are not to often. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-05 : 16:38:10
|
Can you just use this?:select commentuserid as userid, count(*) as commentcountfrom commnetgroup by commentuserid I think it would be indexable, though it is probably not necessary. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-04-06 : 03:27:03
|
kaisdd, if i understand your problem correctly, this is what you want ?CREATE TRIGGER TRCommentDelete ON dbo.pcComment FOR DELETE ASBEGIN UPDATE u SET u.UserCommentCount = u.UserCommentCount - d.cnt FROM pcUser u inner join ( SELECT CommentAuthorID, count(*) as cnt FROM deleted GROUP BY CommentAuthorID ) d ON u.UserID = d.CommentAuthorIDEND KH |
|
|
kaisdd
Starting Member
17 Posts |
Posted - 2006-04-06 : 10:51:08
|
thanks for your response!trigger works fine. i think i stay with it. ... maybe because i didn´t get that indexed view thing yet ... |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-06 : 12:51:23
|
Ughh....... |
|
|
|