/*
** Triggers
*/

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Active_insert]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Active_insert]
go

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Forum_update]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Forum_update]
go

/*
CREATE TRIGGER [{databaseOwner}].[{objectQualifier}Forum_update] ON [{databaseOwner}].[{objectQualifier}Forum] FOR UPDATE AS
BEGIN
	IF UPDATE(LastTopicID) OR UPDATE(LastMessageID)
	BEGIN	
		-- recursively update the forum
		DECLARE @ParentID int		

		SET @ParentID = (SELECT TOP 1 ParentID FROM inserted)
		
		WHILE (@ParentID IS NOT NULL)
		BEGIN
			UPDATE a SET
				a.LastPosted = b.LastPosted,
				a.LastTopicID = b.LastTopicID,
				a.LastMessageID = b.LastMessageID,
				a.LastUserID = b.LastUserID,
				a.LastUserName = b.LastUserName
			FROM
				[{databaseOwner}].[{objectQualifier}Forum]] a, inserted b
			WHERE
				a.ForumID = @ParentID AND ((a.LastPosted < b.LastPosted) OR a.LastPosted IS NULL);
			
			SET @ParentID = (SELECT ParentID FROM [{databaseOwner}].[{objectQualifier}Forum] WHERE ForumID = @ParentID)
		END
	END
END
*/
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Group_update]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Group_update]
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}Group_insert]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}Group_insert]
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}UserGroup_insert]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}UserGroup_insert]
GO

if exists(select 1 from sysobjects where id=object_id(N'[{databaseOwner}].[{objectQualifier}UserGroup_delete]') and objectproperty(id, N'IsTrigger') = 1)
	drop trigger [{databaseOwner}].[{objectQualifier}UserGroup_delete]
GO

-- update Forum table with last topic/post info
	update [dbo].[yaf_Forum] set
		LastPosted = @Posted,
		LastTopicID = @TopicID,
		LastMessageID = @MessageID,
		LastUserID = @UserID,
		LastUserName = @UserName
	where ForumID = @ForumID

USE [yourforumdb]
GO
/****** Object:  UserDefinedFunction [dbo].[yaf_forum_lasttopic]    Script Date: 04/11/2009 14:38:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[yaf_forum_lasttopic] 

(	
	@ForumID int,
	@UserID int = null,
	@LastTopicID int = null,
	@LastPosted datetime = null
) RETURNS int AS
BEGIN
	-- local variables for temporary values
	declare @SubforumID int
	declare @TopicID int
	declare @Posted datetime

	-- try to retrieve last direct topic posed in forums if not supplied as argument 
	if (@LastTopicID is null or @LastPosted is null) begin
		SELECT 
			@LastTopicID=a.LastTopicID,
			@LastPosted=a.LastPosted
		FROM
			[dbo].[yaf_Forum] a
			JOIN [dbo].[yaf_vaccess] x ON a.ForumID=x.ForumID
		WHERE
			a.ForumID=@ForumID and
			(
				(@UserID is null and (a.Flags & 2)=0) or 
				(x.UserID=@UserID and ((a.Flags & 2)=0 or x.ReadAccess<>0))
			)
	end

	-- look for newer topic/message in subforums
	if exists(select 1 from [dbo].[yaf_Forum] where ParentID=@ForumID)

	begin
		declare c cursor for
			SELECT 
				a.ForumID,
				a.LastTopicID,
				a.LastPosted
			FROM
				[dbo].[yaf_Forum] a
				JOIN [dbo].[yaf_vaccess] x ON a.ForumID=x.ForumID
			WHERE
				a.ParentID=@ForumID and
				(
					(@UserID is null and (a.Flags & 2)=0) or 
					(x.UserID=@UserID and ((a.Flags & 2)=0 or x.ReadAccess<>0))
				)
			
		open c
		
		-- cycle through subforums
		fetch next from c into @SubforumID, @TopicID, @Posted
		while @@FETCH_STATUS = 0
		begin
			-- get last topic/message info for subforum
			SELECT 
				@TopicID = LastTopicID,
				@Posted = LastPosted
			FROM
				[dbo].[yaf_forum_lastposted](@SubforumID, @UserID, @TopicID, @Posted)


			-- if subforum has newer topic/message, make it last for parent forum
			if (@TopicID is not null and @Posted is not null and (@LastPosted < @Posted OR (@LastPosted is null and @Posted is not null ) )) begin
				SET @LastTopicID = @TopicID
				SET @LastPosted = @Posted
			end

			fetch next from c into @SubforumID, @TopicID, @Posted
		end
		close c
		deallocate c
	end

	-- return id of topic with last message in this forum or its subforums
	RETURN @LastTopicID
END