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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 trigger to update a count

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 = 8

my current trigger looks like that:

CREATE TRIGGER TRCommentDelete ON dbo.pcComment FOR DELETE AS
BEGIN
UPDATE pcUser SET UserCommentCount = UserCommentCount - 1 FROM deleted WHERE pcUser.UserID = deleted.CommentAuthorID
END

problem 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.
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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 user

should 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.

Go to Top of Page

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 commentcount
from commnet
group by commentuserid
I think it would be indexable, though it is probably not necessary.
Go to Top of Page

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 AS
BEGIN
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.CommentAuthorID
END




KH


Go to Top of Page

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 ...
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-06 : 12:51:23
Ughh.......
Go to Top of Page
   

- Advertisement -