Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - SQL Trigger
  FAQ FAQ  Forum Search   Register Register  Login Login

SQL Trigger

 Post Reply Post Reply
Author
TechSquared View Drop Down
Newbie
Newbie


Joined: 01 March 2006
Location: United States
Status: Offline
Points: 15
Post Options Post Options   Thanks (0) Thanks(0)   Quote TechSquared Quote  Post ReplyReply Direct Link To This Post Topic: SQL Trigger
    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.
Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post 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 administrator@domain.com.


Edited by Desperado - 05 December 2007 at 8:59pm
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com

Back to Top
TechSquared View Drop Down
Newbie
Newbie


Joined: 01 March 2006
Location: United States
Status: Offline
Points: 15
Post Options Post Options   Thanks (0) Thanks(0)   Quote TechSquared Quote  Post ReplyReply Direct Link To This Post 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?

Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

Joined: 27 January 2005
Location: United States
Status: Offline
Points: 1143
Post Options Post Options   Thanks (0) Thanks(0)   Quote Desperado Quote  Post ReplyReply Direct Link To This Post 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

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.156 seconds.