Print Page | Close Window

SQL Trigger

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6320
Printed Date: 14 July 2025 at 5:49pm


Topic: SQL Trigger
Posted By: TechSquared
Subject: SQL Trigger
Date Posted: 05 December 2007 at 5:07pm
I wanted to share a trigger that I installed that has helped keep my quarantine database manageable and see if anyone has a better idea to help make the quarantine smaller.
 
I keep the quarantined email for 10 full days, in case someone is on vacation.  My sql database is around 1 gig.
 
The basic idea is to instantly (or close to it) delete any email quarantined that is not associated with a known user.
 
The trigger I use checks the login table to see if the user exists, otherwise it marks it for deletion.
 
IF (SELECT COUNT(*) FROM [dbo].[tblLogins], [inserted]
    WHERE [dbo].[tblLogins].[EMail] = [inserted].[EmailTo]) < 1
BEGIN
    UPDATE [dbo].[tblQuarantine]
        SET [Expire] = 1
        WHERE [QuarID] = @@IDENTITY
END
 
Please let me know if you have a better way so I can make the database even smaller.


Replies:
Posted By: Desperado
Date Posted: 05 December 2007 at 8:26pm
There are many ways to skin a cat (what a horrible expression!).  However, at first glance, this should work.
 
I actually use something more along the line of:
 
select count(*) from tblQuarantine
where EmailTo not in (select Email from tblLogins)
 
But mine get even more complex as I have users that manage muntiple addresses and have a generic login like mailto:administrator@domain.com - administrator@domain.com .


-------------
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com

Posted By: TechSquared
Date Posted: 06 December 2007 at 7:06am

If you don't mind my asking, what do you use to combine those accounts into a single user able to check the junk mail.  I figured I could do that though my own customization, but is there an easy way without disturbing the rest of the software?

Posted By: Desperado
Date Posted: 06 December 2007 at 11:22am
OK ... It is not easy but not very hard either.  My asp gui does all the work.  I did, however add a table to link the "special" users to the domains/accounts they can manage.  I can give you more information by PM but not today as I am in the middle of my 32nd hour straight without sleep and am very fuzzy. Remind me sometime.

-------------
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com


Print Page | Close Window