Help with delete trigger... - 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)

Help with delete trigger...

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-07-05 : 17:14:52

I have a forums system consisting mainly of two tables called forum_root for new topics and forum_children for replies. I created Insert and Delete triggers on these tables so that I can list aggregate data such as the number of posts a user has made and increment or decrement this number as posts are added or deleted.

Here is the problem. I just tried to add a feature so an admin can delete a thread. The proc for this command contains three delete statements:

DELETE FROM forum_my_threads
WHERE root_id = @root_id

DELETE FROM forum_children
WHERE root_id = @root_id

DELETE FROM forum_root
WHERE root_id = @root_id

But the delete from forum_children statement fails because of the trigger: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. "

Here is the trigger.

CREATE TRIGGER [tr_forum_children_del] ON [dbo].[forum_children]
FOR DELETE
AS

UPDATE forums
SET num_replies = num_replies - 1
WHERE forum_id = (SELECT forum_id
FROM deleted)

UPDATE members
SET forum_post_count = forum_post_count - 1
WHERE member_id = (SELECT member_id
FROM deleted)

-----------------------------------------------------

Does anyone have any ideas of how to remedy this situation? I really like the triggers because it ensures that my aggregate data stays up to date. I will not be deleting threads very often. Should I try to somehow loop through each child post corresponding to the root post and delete it individually? This should enable my trigger to work correctly. In essence:

SELECT child_id FROM forum_children WHERE root_id = @root_id
-- For above result set
DELETE FROM forum_children WHERE child_id = @child_id
-- Loop

If this would be the best method does anyone have an idea on how best to do this? Is there an easier way than using a temp table and looping through the table?

Thanks,
Chris

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-05 : 21:04:27

I'm assuming this is because you are deleting several records

try
UPDATE forums
SET num_replies = num_replies - (select count(*) from deleted where forums.forim_id = deleted.forum_id)
WHERE forum_id in (SELECT distinct forum_id FROM deleted)

UPDATE members
SET forum_post_count = forum_post_count - (select count(*) from deleted where forums.member_id = deleted.member_id)
WHERE member_id in (SELECT distinct member_id FROM deleted)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources