SQL Server 2000 trigger problem

[ChrisQuick]

I have a collection of tables used for an online discussion forum.

We have 3 tables:
SupportForums - List of Forums.
SupportForumTopics - List of Messages, by forum.
SupportForumsReplies - List of Message replies by Forum & Topic.

I have the following trigger on the table SupportForumsReplies:

CREATE TRIGGER [AddToTopicReplyTotal] ON dbo.SupportForumsReplies
FOR INSERT
AS

/* Update the count for the total number of messages in the forum */
Update SupportForums Set SupportMessageTotal = ISNULL(SupportMessageTotal, 0) + 1, SupportLastMessageDate = getDate()
where SupportForum_id = (Select SupportForum_Id from inserted)

/* Update the count for the total number of replies to this individual thread */
Update SupportForumTopics Set SupportMessageResponseTotal = ISNULL(SupportMessageResponseTotal, 0) + 1
where SupportForumTopics.SupportForumsTopic_id = (Select SupportForumsTopic_id from inserted)


/*Update the SupportForumTopics table for this record to show the LastMessageDate*/
Update SupportForumTopics Set SupportLastMessageDate = getDate()
where SupportForumTopics.SupportForumsTopic_id = (Select SupportForumsTopic_id from inserted)


/*Update this table for this record to show the LastMessageDate*/
Update SupportForumsReplies Set SupportForumSubmittedDate = getDate()
where SupportForumReply_id = (Select SupportForumReply_id from inserted)


The problem is the update statements are effecting EVERY record in the SupportForumTopics table even though our where clause should be restricting the update statement to the SupportForumTopics.SupportForumsTopic_id referenced.

Any thoughts??? cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle

 

[rajeevnandanmishra]

Hi Chris,
Try to do like following:
CREATE TRIGGER [AddToTopicReplyTotal] ON dbo.SupportForumsReplies
FOR INSERT
AS
/* Update the count for the total number of messages in the forum */
Update SupportForums Set SupportMessageTotal = ISNULL(SupportMessageTotal, 0) + 1, SupportLastMessageDate = getDate()
FROM SupportForums a, inserted i
where a.SupportForum_id = i.SupportForum_Id

/* Update the count for the total number of replies to this individual thread */
Update SupportForumTopics Set SupportMessageResponseTotal = ISNULL(SupportMessageResponseTotal, 0) + 1
FROM SupportForumTopics a, inserted i
where a.SupportForumsTopic_id = i.SupportForumsTopic_id

/*Update the SupportForumTopics table for this record to show the LastMessageDate*/
Update SupportForumTopics Set SupportLastMessageDate = getDate()
FROM SupportForumTopics a, inserted i
where a.SupportForumsTopic_id = i.SupportForumsTopic_id

/*Update this table for this record to show the LastMessageDate*/
Update SupportForumsReplies Set SupportForumSubmittedDate = getDate()
FROM SupportForumsReplies a, inserted i
where a.SupportForumReply_id = i.SupportForumReply_id

 

[ChrisQuick]

Thank you!! That fixed it.
cquick@geotg.com
Geographic Information System (GIS), ASP, some Oracle