Name: E-mail on insert of unmoderated message
Version: 1.0
Snitz Version: 3.303
Database Type: SQL Server 7.0/2000
Author: Michael Sumerano
Support: MOD support forum
Install: Must have Query Analyzer or Enterprise Manager access to your SQL server. Must have the SMTP Service installed on the same server as your SQL Server OR use a different SMTP component like ASPEmail.
This MOD is NOT a mod of the Snitz forum code, but an addition to the SQL Server forum database, specifically to the FORUM_TOPICS and FORUM_REPLY tables. The following code will set up two triggers, one on each of the tables mentioned above that will e-mail moderators of a forum when an unapproved topic or reply is posted. This modification has been invaluable to our company since there is nowhere in the code to actually notify a moderator when unapproved messages appear in their forums.
[1] The first modification is a stored procedure in your MASTER database that will perform the actual sending of the e-mail. This code is basically pulled from SQLTeam.com. Mine uses Microsoft's SMTP service and the CDONTS.NewMail component, but you can use any SMTP component. Just change the code to set the properties that are specific to the SMTP component you are using.
Create Procedure sp_SMTPMail
@SenderAddress varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'CDONTS.NewMail', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'From', @SenderAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'To', @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'Body', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'Send', NULL
EXEC sp_OADestroy @oMail
END
set nocount off
GO
[2] The next modification is to add a trigger to the FORUM_TOPICS table. This will actually "trigger" the e-mail event when an unmoderated topic is inserted into the FORUM_TOPICS table. Make sure to change the @From variable to the e-mail address that you want the e-mail to come from.
CREATE TRIGGER tr_FORUM_TOPICS_INSERT
ON dbo.FORUM_TOPICS
FOR INSERT
AS
IF (SELECT T_STATUS FROM inserted WHERE T_STATUS = 2) = 2
BEGIN
DECLARE EmailModerators CURSOR FOR
select t.topic_id, m_email, c.cat_name, f.f_subject
from inserted t
inner join forum_moderator mo on t.forum_id = mo.forum_id
inner join forum_members me on mo.member_id = me.member_id
inner join forum_category c on t.cat_id = c.cat_id
inner join forum_forum f on t.forum_id = f.forum_id
DECLARE @From varchar(255)
DECLARE @Topic_Id int
DECLARE @To varchar(255)
DECLARE @CatName varchar(255)
DECLARE @ForumName varchar(255)
DECLARE @Subject varchar(255)
DECLARE @Body varchar(8000)
SET @From = 'you@yourdomain.com'
SET @Subject = 'NEW, UNMODERATED topic in Your Forum'
OPEN EmailModerators
FETCH NEXT FROM EmailModerators INTO @Topic_Id, @To, @CatName, @ForumName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Body = 'An unapproved topic has been posted in the ' + @ForumName + ' - ' + @CatName + ' forum. Please visit the forum and APPROVE, HOLD, or DELETE the message. ' + 'http://yourdomain.com/forum/link.asp?TOPIC_ID=' + cast(@Topic_Id as varchar(255))
exec sp_SMTPMail @From, @To, @Subject, @Body
FETCH NEXT FROM EmailModerators INTO @Topic_Id, @To, @CatName, @ForumName
END
CLOSE EmailModerators
DEALLOCATE EmailModerators
END
[3] The last modification is to add a trigger to the FORUM_REPLY table. This will actually "trigger" the e-mail event when an unmoderated reply is inserted into the FORUM_REPLY table. Make sure to change the @From variable to the e-mail address that you want the e-mail to come from.
CREATE TRIGGER tr_FORUM_REPLY_INSERT
ON dbo.FORUM_REPLY
FOR INSERT
AS
IF (SELECT R_STATUS FROM inserted WHERE R_STATUS = 2) = 2
BEGIN
DECLARE EmailModerators CURSOR FOR
select r.topic_id, me.m_email, c.cat_name, f.f_subject
from inserted r
inner join forum_moderator mo on r.forum_id = mo.forum_id
inner join forum_members me on mo.member_id = me.member_id
inner join forum_category c on r.cat_id = c.cat_id
inner join forum_forum f on r.forum_id = f.forum_id
DECLARE @Topic_Id int
DECLARE @To varchar(255)
DECLARE @From varchar(255)
DECLARE @CatName varchar(255)
DECLARE @ForumName varchar(255)
DECLARE @Subject varchar(255)
DECLARE @Body varchar(8000)
SET @From = 'you@yourdomain.com'
SET @Subject = 'NEW, UNMODERATED reply in Your Forum'
OPEN EmailModerators
FETCH NEXT FROM EmailModerators INTO @Topic_Id, @To, @CatName, @ForumName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Body = 'An unapproved reply has been posted in the ' + @CatName + ' - ' + @ForumName + ' forum. Please visit the forum and APPROVE, HOLD, or DELETE the message. ' + 'http://yourdomain.com/forum/link.asp?TOPIC_ID=' + cast(@Topic_Id as varchar(255))
exec sp_SMTPMail @From, @To, @Subject, @Body
FETCH NEXT FROM EmailModerators INTO @Topic_Id, @To, @CatName, @ForumName
END
CLOSE EmailModerators
DEALLOCATE EmailModerators
END
More info to follow based on any questions! 
Mike
http://www.ekeystone.com/
http://www.sumovw.com/
http://www.driverfx.com/
Topic Locked
Posted - 12 November 2001 : 16:47:33
