CREATE TRIGGER [TR_DiscussionPosts_Insert] on dbo.[DiscussionPosts]
FOR INSERT
AS
begin
set nocount on
declare @ReplyToID integer
declare @PostDate datetime
declare @Reply integer
declare @MaxSeq integer
declare @IndentLevel integer
declare @TopicID integer
select @Reply = 0
select @ReplyToID = ReplyToID from inserted
select @PostDate = PostDate from inserted
select @TopicID = TopicID from inserted
if @ReplyToID is not null
begin
select @Reply = 1
select @IndentLevel = (select IndentLevel + 1 from DiscussionPosts where ID=@ReplyToID)
if @IndentLevel = 1
select @MaxSeq = (select max(sequence) from DiscussionPosts where TopicID = @TopicID) + 1
else
begin
-- Find the sequence of the last sibling
select @MaxSeq = coalesce(min(sequence),(select sequence+1 from DiscussionPosts where id=@ReplyToID)) from DiscussionPosts where TopicID=@TopicID
and IndentLevel<= (select IndentLevel from DiscussionPosts where id=@ReplyToID)
and sequence > (select sequence from DiscussionPosts where id=@ReplyToID)
update DiscussionPosts set sequence = sequence + 1 where TopicID = @TopicID and sequence >= @MaxSeq
end
update DiscussionPosts set
DiscussionPosts.IndentLevel=@IndentLevel,
DiscussionPosts.Sequence = @MaxSeq
from DiscussionPosts inner join inserted on DiscussionPosts.ID = inserted.ID
Where DiscussionPosts.IndentLevel = 0
end
update DiscussionTopics set
DiscussionTopics.Replies = DiscussionTopics.Replies + @Reply, DiscussionTopics.LastPostDate = @PostDate
from DiscussionTopics inner join inserted on DiscussionTopics.ID = inserted.TopicID
end
|
Trigger Definition
