Triggers question... - SQL Server Forums

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

SQL Server Development (2000)

Triggers question...

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-06-13 : 17:15:42

I have a table called forum_root and another table called forums. The forums table contains information about each forum such as:

FORUMS
forum_id
forum_desc
.
num_threads
num_replies

Basically, I want the forums table to store aggregate values for the number of threads and replies within each forum. I can do a count * on my forum_root and my forum_children table to get these values. Like this:

SELECT COUNT(*) as threads
,forum_id
FROM forum_root
GROUP BY forum_id
ORDER BY forum_id ASC

SELECT COUNT(*) as replies
,forum_id
FROM forum_children
GROUP BY forum_id
ORDER BY forum_id ASC

What I can't figure out how to do is how to populate my forums table with the aggregate values the first time. There are only 19 forums or so and I could just write a proc and run it over and over with the forum_id as the input. But I am also going to implement a post_count feature for members and will have to do the same thing 25,000+ members so I am looking for a better solution.

So basically the proc above retrieves the info but how can I interate through each row (for each forum_id) and update the num_threads and num_replies field for that forum_id in the forums table?

Thanks,
Chris

P.S. Thanks for the great article on triggers! I have already set up my forum_root and forum_children table to update my num_threads and num_replies fields in my forums table on insert and delete as well as my post_count field in my members table.

Edited by - cbrinson on 06/13/2002 17:18:31

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-06-13 : 17:38:45

A-HA! It's a trick! Your question isn't really about triggers, even though you say it is in the subject line. Very sneaky...

OK, so you've got the triggers part figured out, thanks to the awesome articles on SQLTeam. Good job. Now, as for the initial update, I would suggest something along the lines of using a temporary table and doing an INSERT... SELECT statement where the SELECT is one of your existing selects. Then, join the temporary table to the table that you want updated, join on Forum_ID, and issue your update statement. Something along the lines of

UPDATE Forums
SET num_threads = #temp.TotalThreads
FROM Forums
Join #temp on Forums.Forum_ID = #Temp.Forum_ID

Forgive my sloppiness (test the code!), I've been inundated by Content Management Systems the last few weeks and my SQL is a little rusty...

cbrinson
Starting Member

36 Posts

Posted - 2002-06-13 : 17:52:54

Hehe. I actually started typing a question about triggers but found the articles and answered my own question. Forgot to change the subject though. Doh!

Chris

izaltsman
A custom title

1139 Posts

Posted - 2002-06-13 : 17:55:25

Or you could bypass the temp table if you want.


UPDATE f
SET 
    num_threads = th_count.threads
    , num_replies = rep_count.replies
FROM
    FORUMS f
    INNER JOIN (SELECT forum_id
                       , COUNT(*) as threads 
                FROM FORUM_ROOT 
                GROUP BY forum_id) as th_count
        ON f.forum_id = th_count.forum_id
    INNER JOIN (SELECT forum_id
                       , COUNT(*) as replies 
                FROM FORUM_CHILDREN 
                GROUP BY forum_id) as rep_count
        ON f.forum_id = rep_count.forum_id

cbrinson
Starting Member

36 Posts

Posted - 2002-06-13 : 18:02:37

Holy crap!! You are the SQL pimp. I just copied that script, looked it over for a minute or so, executed it and it was perfect! First try. Man, I have a lot to learn about SQL. Thanks a ton!

Take care,
Chris

izaltsman
A custom title

1139 Posts

Posted - 2002-06-14 : 09:21:42

quote:

Holy crap!! You are the SQL pimp.

Nah... Rob is the pimp. I am more of a SQL whore!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-06-14 : 12:12:55

quote:

quote:

Holy crap!! You are the SQL pimp.

Nah... Rob is the pimp. I am more of a SQL whore!

A few more posts, Ilya, and you'll be a Post-Master General, or whatever you want... Keep up the great work!

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-06-14 : 14:27:54

And of course, don't confuse either of these guys with... Justin Bigelow, SQL Gigolo.

cbrinson
Starting Member

36 Posts

Posted - 2002-06-14 : 16:00:57

By the way, in case you guys are curious here is the forums system that I have developed with a lot of help from you guys:
[url]http://hondaprelude.com/forums/[/url]

I rolled that member post count/faster thread count and post count by forum code over last night. I still need to spend some time working on the compose form adding the cool little javascript editing pad etc. I am not very good at javascript so I have put that off for a long time. Anyway, it is not the most advanced forums system but the code is so much nicer than the snitz stuff. No offense intended to anyone that might work on that forum. It uses all stored procs, COM components and really tidy ASP code rather than the spaghetti of Snitz. I have been running that site for over 3 years and it receives about 8 Million page requests a month. Of course, it is still losing money as I am a complete business idiot.

Thanks as always for the help!
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources